Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table test_table (
  id serial primary key,
  status_code text, 
  priority int, 
  attempts int, 
  creation_time timestamptz,
  queue text not null
 )
create index on test_table (queue, status_code)
-- nh2's optimal query
EXPLAIN ANALYZE
SELECT *
FROM test_table
WHERE ("status_code" = (CAST(E'pending' AS text))) AND (("queue") = (CAST(E'myqueue' AS text))) 
ORDER BY "priority" DESC NULLS FIRST, 
         "attempts" ASC NULLS LAST, 
         "creation_time" ASC NULLS LAST 
LIMIT 1; 


QUERY PLAN


Limit  (cost=8.18..8.19 rows=1 width=84) (actual time=0.059..0.060 rows=0 loops=1)

  ->  Sort  (cost=8.18..8.19 rows=1 width=84) (actual time=0.057..0.058 rows=0 loops=1)

        Sort Key: priority DESC, attempts, creation_time

        Sort Method: quicksort  Memory: 25kB

        ->  Index Scan using test_table_queue_status_code_idx on test_table  (cost=0.15..8.17 rows=1 width=84) (actual time=0.006..0.006 rows=0 loops=1)

              Index Cond: ((queue = 'myqueue'::text) AND (status_code = 'pending'::text))

Planning Time: 0.876 ms

Execution Time: 0.129 ms


-- nh2's current querey
EXPLAIN ANALYZE
SELECT
  "id0_1" as "result1_2"
  FROM (SELECT
        *
        FROM (SELECT
              *
              FROM (SELECT
                    *
                    FROM (SELECT
                          *
                          FROM (SELECT
                                *
                                FROM (SELECT
                                      "id" as "id0_1",
                                      "queue" as "queue1_1",
                                      "priority" as "priority2_1",
                                      "creation_time" as "creation_time3_1",
                                      "status_code" as "status_code5_1",
                                      "attempts" as "attempts6_1"
                                      FROM test_table as "T1") as "T1") as "T1"
                          ORDER BY "priority2_1" DESC NULLS FIRST,
                                   "attempts6_1" ASC NULLS LAST,
                                   "creation_time3_1" ASC NULLS LAST) as "T1"
                    WHERE (("status_code5_1") = (CAST(E'pending' AS text))) AND (("queue1_1") = (CAST(E'myqueue' AS text)))) as "T1"
              LIMIT 1) as "T1") as "T1"


QUERY PLAN


Subquery Scan on "T1"  (cost=8.18..8.21 rows=1 width=4) (actual time=0.041..0.042 rows=0 loops=1)

  ->  Limit  (cost=8.18..8.20 rows=1 width=84) (actual time=0.041..0.041 rows=0 loops=1)

        ->  Subquery Scan on "T1_1"  (cost=8.18..8.20 rows=1 width=84) (actual time=0.040..0.041 rows=0 loops=1)

              ->  Sort  (cost=8.18..8.19 rows=1 width=84) (actual time=0.040..0.040 rows=0 loops=1)

                    Sort Key: "T1_2".priority DESC, "T1_2".attempts, "T1_2".creation_time

                    Sort Method: quicksort  Memory: 25kB

                    ->  Index Scan using test_table_queue_status_code_idx on test_table "T1_2"  (cost=0.15..8.17 rows=1 width=84) (actual time=0.019..0.019 rows=0 loops=1)

                          Index Cond: ((queue = 'myqueue'::text) AND (status_code = 'pending'::text))

Planning Time: 0.329 ms

Execution Time: 0.103 ms


-- The query we'd get if we restricted before sorting
EXPLAIN ANALYZE
SELECT
  "id0_1" as "result1_2"
  FROM (SELECT
        *
        FROM (SELECT
              *
              FROM (SELECT
                    *
                    FROM (SELECT
                          *
                          FROM (SELECT
                                *
                                FROM (SELECT
                                      "id" as "id0_1",
                                      "queue" as "queue1_1",
                                      "priority" as "priority2_1",
                                      "creation_time" as "creation_time3_1",
                                      "status_code" as "status_code5_1",
                                      "attempts" as "attempts6_1"
                                      FROM test_table as "T1") as "T1") as "T1"
                          WHERE (("status_code5_1") = (CAST(E'pending' AS text))) AND (("queue1_1") = (CAST(E'myqueue' AS text)))) as "T1"
                    ORDER BY "priority2_1" DESC NULLS FIRST,
                                   "attempts6_1" ASC NULLS LAST,
                                   "creation_time3_1" ASC NULLS LAST) as "T1"
              LIMIT 1) as "T1") as "T1"


QUERY PLAN


Subquery Scan on "T1"  (cost=8.18..8.21 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)

  ->  Limit  (cost=8.18..8.20 rows=1 width=84) (actual time=0.009..0.010 rows=0 loops=1)

        ->  Subquery Scan on "T1_1"  (cost=8.18..8.20 rows=1 width=84) (actual time=0.009..0.009 rows=0 loops=1)

              ->  Sort  (cost=8.18..8.19 rows=1 width=84) (actual time=0.009..0.009 rows=0 loops=1)

                    Sort Key: "T1_2".priority DESC, "T1_2".attempts, "T1_2".creation_time

                    Sort Method: quicksort  Memory: 25kB

                    ->  Index Scan using test_table_queue_status_code_idx on test_table "T1_2"  (cost=0.15..8.17 rows=1 width=84) (actual time=0.005..0.005 rows=0 loops=1)

                          Index Cond: ((queue = 'myqueue'::text) AND (status_code = 'pending'::text))

Planning Time: 0.232 ms

Execution Time: 0.049 ms


-- The query we'd get if we combined sorting and limiting
EXPLAIN ANALYZE
SELECT
  "id0_1" as "result1_2"
  FROM (SELECT
        *
        FROM (SELECT
              *
              FROM (SELECT
                    *
                    FROM (SELECT
                          *
                          FROM (SELECT
                                *
                                FROM (SELECT
                                      "id" as "id0_1",
                                      "queue" as "queue1_1",
                                      "priority" as "priority2_1",
                                      "creation_time" as "creation_time3_1",
                                      "status_code" as "status_code5_1",
                                      "attempts" as "attempts6_1"
                                      FROM test_table as "T1") as "T1") as "T1"
                          WHERE (("status_code5_1") = (CAST(E'pending' AS text))) AND (("queue1_1") = (CAST(E'myqueue' AS text)))) as "T1"
                    ORDER BY "priority2_1" DESC NULLS FIRST,
                                   "attempts6_1" ASC NULLS LAST,
                                   "creation_time3_1" ASC NULLS LAST
                    LIMIT 1) as "T1"
              ) as "T1") as "T1"


QUERY PLAN


Subquery Scan on "T1"  (cost=8.18..8.20 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=1)

  ->  Limit  (cost=8.18..8.19 rows=1 width=84) (actual time=0.022..0.022 rows=0 loops=1)

        ->  Sort  (cost=8.18..8.19 rows=1 width=84) (actual time=0.021..0.021 rows=0 loops=1)

              Sort Key: "T1_1".priority DESC, "T1_1".attempts, "T1_1".creation_time

              Sort Method: quicksort  Memory: 25kB

              ->  Index Scan using test_table_queue_status_code_idx on test_table "T1_1"  (cost=0.15..8.17 rows=1 width=84) (actual time=0.017..0.017 rows=0 loops=1)

                    Index Cond: ((queue = 'myqueue'::text) AND (status_code = 'pending'::text))

Planning Time: 0.162 ms

Execution Time: 0.046 ms