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 FUNCTION
CREATE TABLE
INSERT 0 100000
QUERY PLAN |
---|
Seq Scan on users (cost=0.00..2032.39 rows=2865 width=100) |
Filter: ((email)::text ~ 'OAK'::text) |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=2196.90..2204.06 rows=2865 width=100) |
Sort Key: name |
-> Seq Scan on users (cost=0.00..2032.39 rows=2865 width=100) |
Filter: ((email)::text ~ 'OAK'::text) |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=2196.90..2204.06 rows=2865 width=100) (actual time=55.759..55.761 rows=9 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 26kB |
-> Seq Scan on users (cost=0.00..2032.39 rows=2865 width=100) (actual time=1.211..55.608 rows=9 loops=1) |
Filter: ((email)::text ~ 'OAK'::text) |
Rows Removed by Filter: 99991 |
Planning Time: 0.042 ms |
Execution Time: 55.779 ms |
EXPLAIN
CREATE INDEX
QUERY PLAN |
---|
Sort (cost=2626.32..2636.32 rows=4000 width=100) (actual time=51.004..51.007 rows=9 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 26kB |
-> Seq Scan on users (cost=0.00..2387.00 rows=4000 width=100) (actual time=1.093..50.974 rows=9 loops=1) |
Filter: ((email)::text ~ 'OAK'::text) |
Rows Removed by Filter: 99991 |
Planning Time: 0.304 ms |
Execution Time: 51.033 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=2626.32..2636.32 rows=4000 width=100) (actual time=50.238..50.240 rows=9 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 26kB |
-> Seq Scan on users (cost=0.00..2387.00 rows=4000 width=100) (actual time=1.235..50.210 rows=9 loops=1) |
Filter: ((email)::text ~ 'OAK'::text) |
Rows Removed by Filter: 99991 |
Planning Time: 0.192 ms |
Execution Time: 50.267 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=942.34..943.59 rows=500 width=100) (actual time=0.054..0.055 rows=0 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on users (cost=12.29..919.93 rows=500 width=100) (actual time=0.049..0.050 rows=0 loops=1) |
Recheck Cond: ((email)::text = 'OAK'::text) |
-> Bitmap Index Scan on users_email_index (cost=0.00..12.17 rows=500 width=0) (actual time=0.047..0.047 rows=0 loops=1) |
Index Cond: ((email)::text = 'OAK'::text) |
Planning Time: 0.134 ms |
Execution Time: 0.095 ms |
EXPLAIN
DROP INDEX
CREATE INDEX
QUERY PLAN |
---|
Sort (cost=1473.32..1483.32 rows=4000 width=100) (actual time=0.297..0.299 rows=9 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 26kB |
-> Bitmap Heap Scan on users (cost=47.00..1234.00 rows=4000 width=100) (actual time=0.116..0.271 rows=9 loops=1) |
Recheck Cond: ((email)::text ~ 'OAK'::text) |
Heap Blocks: exact=9 |
-> Bitmap Index Scan on users_email_index (cost=0.00..46.00 rows=4000 width=0) (actual time=0.062..0.062 rows=9 loops=1) |
Index Cond: ((email)::text ~ 'OAK'::text) |
Planning Time: 0.372 ms |
Execution Time: 0.332 ms |
EXPLAIN