Postgres 11

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 mytable
    ("course_id" varchar(7), "sec_id" int, "semester" varchar(4), "year" int, "count" int);
    
INSERT INTO mytable VALUES
    ('CS-101', 1, 'Fall', 2009, 6),
    ('CS-347', 1, 'Fall', 2009, 2),
    ('PHY-101', 1, 'Fall', 2009, 1);
3 rows affected

INSERT INTO mytable VALUES
    ('AB-42', 1, 'Fall', 2009, 6);

SELECT * FROM mytable
1 rows affected



course_id
sec_id
semester
year
count


CS-101
1
Fall
2009
6

CS-347
1
Fall
2009
2

PHY-101
1
Fall
2009
1

AB-42
1
Fall
2009
6


SELECT
    *
FROM (
    SELECT
        *,
        rank() OVER (ORDER BY count DESC) 
    FROM
        mytable
) s
WHERE rank = 1


course_id
sec_id
semester
year
count
rank


CS-101
1
Fall
2009
6
1

AB-42
1
Fall
2009
6
1


EXPLAIN ANALYZE
SELECT
    *
FROM (
    SELECT
        *,
        rank() OVER (ORDER BY count DESC) 
    FROM
        mytable
) s
WHERE rank = 1


QUERY PLAN


Subquery Scan on s  (cost=61.84..88.24 rows=4 width=72) (actual time=0.032..0.067 rows=2 loops=1)

  Filter: (s.rank = 1)

  Rows Removed by Filter: 2

  ->  WindowAgg  (cost=61.84..77.24 rows=880 width=72) (actual time=0.031..0.065 rows=4 loops=1)

        ->  Sort  (cost=61.84..64.04 rows=880 width=64) (actual time=0.020..0.021 rows=4 loops=1)

              Sort Key: mytable.count DESC

              Sort Method: quicksort  Memory: 25kB

              ->  Seq Scan on mytable  (cost=0.00..18.80 rows=880 width=64) (actual time=0.008..0.009 rows=4 loops=1)

Planning Time: 0.088 ms

Execution Time: 0.119 ms


WITH cte AS (
    SELECT * FROM mytable
)
SELECT
    *
FROM
    cte
WHERE count IN (SELECT max(count) FROM cte)


course_id
sec_id
semester
year
count


CS-101
1
Fall
2009
6

AB-42
1
Fall
2009
6


EXPLAIN ANALYZE
WITH cte AS (
    SELECT * FROM mytable
)
SELECT
    *
FROM
    cte
WHERE count = (SELECT max(count) FROM cte)


QUERY PLAN


CTE Scan on cte  (cost=38.61..58.41 rows=4 width=64) (actual time=0.024..0.026 rows=2 loops=1)

  Filter: (count = $1)

  Rows Removed by Filter: 2

  CTE cte

    ->  Seq Scan on mytable  (cost=0.00..18.80 rows=880 width=64) (actual time=0.008..0.009 rows=4 loops=1)

  InitPlan 2 (returns $1)

    ->  Aggregate  (cost=19.80..19.81 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1)

          ->  CTE Scan on cte cte_1  (cost=0.00..17.60 rows=880 width=4) (actual time=0.000..0.008 rows=4 loops=1)

Planning Time: 0.076 ms

Execution Time: 0.067 ms

course_id	sec_id	semester	year	count
CS-101	1	Fall	2009	6
CS-347	1	Fall	2009	2
PHY-101	1	Fall	2009	1
AB-42	1	Fall	2009	6