Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

ORDER BY pushdown + CTID queries result in out of order results

Closed
  • Is something wrong?
#596 3 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

A pull request for this has already been merged.

  • #605 by @staticlibs — merged

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
cpp, postgresql
Domain
databases

Research direction

Start with the reproduction: a 1000-row table, then SET pg_pages_per_task = 1 with pg_order_pushdown on, and check the lag() out-of-order count. The debug output shows one COPY per ctid range, each with its own ORDER BY, so the per-task results are concatenated without a merge. Look at how the scanner combines task results and how pg_use_ctid_scan splits work. Done means the multi-task result is ordered and matches the no-pushdown plan.

Written by the indexing model from the issue text.

Description

What happens?

With pg_order_pushdown=true and pg_use_ctid_scan=on (the defaults), results with ORDER BY can be returned out of order. The CTID scan pushes down the query's ORDER BY into each individual query, but then the returned rows are not assembled in order. Reassembling the rows needs to use a ordered merge operation to preserve the sort order.

To Reproduce
$ createdb order-test
$ psql order-test
psql (18.3)

order-test=# CREATE TABLE shuffled AS (
  SELECT val, repeat('a', 100) AS pad FROM generate_series(1, 1000) val ORDER BY random()
);
SELECT 1000
order-test=# ANALYZE shuffled;
ANALYZE

This is on master (16737d41bdf9a):

$ ./build/release/duckdb -unsigned --cmd "LOAD 'build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension'"
DuckDB v2.0.0-dev86910 (Development Version, d4c9dfd469)
Enter ".help" for usage hints.
memory D ATTACH 'dbname=order-test' AS pg (TYPE postgres, READ_ONLY);
memory D set pg_pages_per_task = 1;  -- make it easier to see on a small table
memory D SET pg_debug_show_queries = true;  -- I have elided some queries for readability
memory D EXPLAIN SELECT val FROM pg.public.shuffled ORDER BY val;  -- no sort!
╭─ Postgres Scan ──╮
│ Table: shuffled  │
│ Projections: val │
│ ~6,688 rows      │
╰──────────────────╯
memory D SELECT count(*) AS out_of_order
         FROM (
             SELECT val, lag(val) OVER () AS prev
             FROM (SELECT val FROM pg.public.shuffled ORDER BY val)
         ) WHERE val < prev;

...
COPY (SELECT "val" FROM "public"."shuffled" WHERE ctid BETWEEN '(0,0)'::tid AND '(1,0)'::tid ORDER BY "val" IS NULL, "val" ASC NULLS LAST) TO STDOUT (FORMAT "binary");

COPY (SELECT "val" FROM "public"."shuffled" WHERE ctid BETWEEN '(1,0)'::tid AND '(2,0)'::tid ORDER BY "val" IS NULL, "val" ASC NULLS LAST) TO STDOUT (FORMAT "binary");

COPY (SELECT "val" FROM "public"."shuffled" WHERE ctid BETWEEN '(2,0)'::tid AND '(3,0)'::tid ORDER BY "val" IS NULL, "val" ASC NULLS LAST) TO STDOUT (FORMAT "binary");
...

┌──────────────┐
│ out_of_order │
│    int64     │
├──────────────┤
│           17 │
└──────────────┘
memory D set pg_pages_per_task = 100000; -- if they're all returned in a single query, it's fine
memory D SELECT count(*) AS out_of_order
         FROM (
             SELECT val, lag(val) OVER () AS prev
             FROM (SELECT val FROM pg.public.shuffled ORDER BY val)
         ) WHERE val < prev;

COPY (SELECT "val" FROM "public"."shuffled" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid ORDER BY "val" IS NULL, "val" ASC NULLS LAST) TO STDOUT (FORMAT "binary");

┌──────────────┐
│ out_of_order │
│    int64     │
├──────────────┤
│            0 │
└──────────────┘
memory D set pg_pages_per_task = 1;    -- re-enable multiple queries
memory D set pg_order_pushdown = false;  -- without pushdown it's fine
memory D EXPLAIN SELECT val FROM pg.public.shuffled ORDER BY val;
╭─ Order By ────────╮
│ Order By: val ASC │
│ ~6,688 rows       │
╰─────────┬─────────╯
╭─ Postgres Scan ───╮
│ Table: shuffled   │
│ Projections: val  │
│ ~6,688 rows       │
╰───────────────────╯

memory D SELECT count(*) AS out_of_order
         FROM (
             SELECT val, lag(val) OVER () AS prev
             FROM (SELECT val FROM pg.public.shuffled ORDER BY val)
         ) WHERE val < prev;

COPY (SELECT "val" FROM "public"."shuffled" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid) TO STDOUT (FORMAT "binary");

┌──────────────┐
│ out_of_order │
│    int64     │
├──────────────┤
│            0 │
└──────────────┘
OS:

Linux

PostgreSQL Version:

18

DuckDB Version:

16737d41bdf9af5694630379b284cc789ff89d71

DuckDB Client:

cli

Full Name:

Gavin Wahl

Affiliation:

Fusionbox

Have you tried this on the latest main branch?
  • I agree
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
  • I agree
Dominant language
C++
Stars
373
Forks
112
Avg merge
10h 18m
Merged PRs (30d)
26

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from duckdb/duckdb-postgres

All issues in duckdb/duckdb-postgres

Similar issues

More C++ issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.