Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

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

Chiusa
  • Qualcosa non va?
#596 3 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Una pull request collegata è già stata integrata.

  • #605 di @staticlibs — integrata

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
35/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
cpp, postgresql
Ambito
databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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
Lingua principale
C++
Stelle
373
Fork
112
Merge medio
10h 18m
PR unite (30g)
26

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di duckdb/duckdb-postgres

Tutte le issue di duckdb/duckdb-postgres

Issue simili

Altre issue su C++

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.