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 TABLE


create index on test_table (queue, status_code)

CREATE INDEX


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.058..0.059 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.005..0.005 rows=0 loops=1)

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

Planning Time: 0.995 ms

Execution Time: 0.098 ms



EXPLAIN


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.025..0.026 rows=0 loops=1)

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

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

              ->  Sort  (cost=8.18..8.19 rows=1 width=84) (actual time=0.023..0.024 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.013..0.014 rows=0 loops=1)

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

Planning Time: 0.311 ms

Execution Time: 0.079 ms



EXPLAIN