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?.
version |
---|
PostgreSQL 12.0 (Debian 12.0-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit |
28 rows affected
class | roll_number |
---|---|
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 1 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
8 | 2 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 1 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
9 | 2 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 1 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 2 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
10 | 3 |
class | roll_number | mathmark | compmark | englmark | sciemark |
---|---|---|---|---|---|
8 | 2 | 78 | 99 | 89 | 87 |
8 | 1 | 98 | 94 | 88 | 96 |
9 | 1 | 90 | 85 | 85 | 85 |
9 | 2 | 95 | 80 | 30 | 40 |
10 | 2 | 50 | 85 | 95 | 65 |
10 | 1 | 85 | 45 | 90 | 65 |
10 | 3 | 51 | 81 | 61 | 71 |
QUERY PLAN |
---|
GroupAggregate (cost=21.56..21.65 rows=5 width=8) (actual time=0.018..0.020 rows=3 loops=1) |
Group Key: class |
-> Sort (cost=21.56..21.57 rows=5 width=8) (actual time=0.014..0.015 rows=7 loops=1) |
Sort Key: class |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on resultdata (cost=0.00..21.50 rows=5 width=8) (actual time=0.007..0.010 rows=7 loops=1) |
Filter: ((subjects)::text = 'english'::text) |
Rows Removed by Filter: 21 |
Planning Time: 0.072 ms |
Execution Time: 0.037 ms |
ERROR: column t1.englmark does not exist
LINE 19: AND t1.englmark = t2.maxmark -- Error occurs here.
^
class | roll_number | mathmark | englmark |
---|---|---|---|
8 | 2 | 78 | 89 |
9 | 1 | 90 | 85 |
10 | 2 | 50 | 95 |