Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Cerrado
  • ¿Algo no está bien?
#596 3 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Ya se ha fusionado un pull request relacionado.

  • #605 de @staticlibs — fusionado

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
35/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
cpp, postgresql
Área
databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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
Lenguaje dominante
C++
Estrellas
373
Forks
112
Merge medio
9 h 48 min
PR fusionados (30 d)
28

Preparar el entorno

Este proyecto no incluye contenedor de desarrollo, Dockerfile ni guía de contribución, así que la configuración corre por tu cuenta: empieza por su README y consulta nuestra guía para la primera contribución para los pasos generales.

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de duckdb/duckdb-postgres

Todos los issues de duckdb/duckdb-postgres

Issues similares

Más issues de C++

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.