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?.
QUERY PLAN |
---|
Hash Left Join (cost=235.82..2965.60 rows=502 width=1796) (actual time=36.676..222.983 rows=1835 loops=1) |
Hash Cond: ((c.oid = dep.refobjid) AND (a.attnum = dep.refobjsubid)) |
-> Merge Right Join (cost=227.48..235.16 rows=502 width=1192) (actual time=33.909..36.785 rows=1835 loops=1) |
Merge Cond: (co.oid = a.attcollation) |
-> Nested Loop (cost=0.28..114.16 rows=781 width=132) (actual time=0.098..0.106 rows=2 loops=1) |
Join Filter: ((co.collnamespace = nco.oid) AND ((nco.nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))) |
Rows Removed by Join Filter: 17 |
-> Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..59.99 rows=781 width=72) (actual time=0.054..0.059 rows=3 loops=1) |
-> Materialize (cost=0.00..1.06 rows=4 width=68) (actual time=0.009..0.011 rows=6 loops=3) |
-> Seq Scan on pg_namespace nco (cost=0.00..1.04 rows=4 width=68) (actual time=0.012..0.014 rows=15 loops=1) |
-> Sort (cost=227.21..228.46 rows=502 width=1068) (actual time=33.806..34.496 rows=1835 loops=1) |
Sort Key: a.attcollation |
Sort Method: quicksort Memory: 1912kB |
-> Hash Join (cost=60.22..204.69 rows=502 width=1068) (actual time=1.986..27.534 rows=1835 loops=1) |
Hash Cond: (t.typnamespace = nt.oid) |
-> Hash Left Join (cost=59.13..200.88 rows=502 width=1008) (actual time=1.915..26.554 rows=1835 loops=1) |
Hash Cond: ((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)) |
-> Hash Left Join (cost=59.11..198.23 rows=502 width=976) (actual time=1.875..25.832 rows=1835 loops=1) |
Hash Cond: (t.typbasetype = bt.oid) |
Join Filter: (t.typtype = 'd'::"char") |
-> Nested Loop (cost=22.85..153.80 rows=502 width=842) (actual time=1.137..24.235 rows=1835 loops=1) |
-> Hash Join (cost=22.57..126.31 rows=502 width=478) (actual time=1.109..21.695 rows=1835 loops=1) |
Hash Cond: (c.relnamespace = nc.oid) |
-> Hash Join (cost=21.48..121.61 rows=669 width=418) (actual time=0.970..20.570 rows=1835 loops=1) |
Hash Cond: (a.attrelid = c.oid) |
Join Filter: (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) |
Rows Removed by Join Filter: 147 |
-> Seq Scan on pg_attribute a (cost=0.00..93.81 rows=2390 width=345) (actual time=0.046..3.398 rows=2389 loops=1) |
Filter: ((NOT attisdropped) AND (attnum > 0)) |
Rows Removed by Filter: 636 |
-> Hash (cost=18.94..18.94 rows=203 width=77) (actual time=0.280..0.281 rows=206 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 31kB |
-> Seq Scan on pg_class c (cost=0.00..18.94 rows=203 width=77) (actual time=0.015..0.193 rows=206 loops=1) |
Filter: (relkind = ANY ('{r,v,f,p}'::"char"[])) |
Rows Removed by Filter: 201 |
-> Hash (cost=1.05..1.05 rows=3 width=68) (actual time=0.093..0.094 rows=15 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 10kB |
-> Seq Scan on pg_namespace nc (cost=0.00..1.05 rows=3 width=68) (actual time=0.032..0.073 rows=15 loops=1) |
Filter: (NOT pg_is_other_temp_schema(oid)) |
-> Memoize (cost=0.29..0.34 rows=1 width=368) (actual time=0.001..0.001 rows=1 loops=1835) |
Cache Key: a.atttypid |
Cache Mode: logical |
Hits: 1794 Misses: 41 Evictions: 0 Overflows: 0 Memory Usage: 15kB |
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.28..0.33 rows=1 width=368) (actual time=0.004..0.004 rows=1 loops=41) |
Index Cond: (oid = a.atttypid) |
-> Hash (cost=27.68..27.68 rows=687 width=138) (actual time=0.680..0.682 rows=659 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 119kB |
-> Hash Join (cost=1.09..27.68 rows=687 width=138) (actual time=0.066..0.377 rows=659 loops=1) |
Hash Cond: (bt.typnamespace = nbt.oid) |
-> Seq Scan on pg_type bt (cost=0.00..22.87 rows=687 width=78) (actual time=0.007..0.091 rows=659 loops=1) |
-> Hash (cost=1.04..1.04 rows=4 width=68) (actual time=0.023..0.024 rows=15 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 10kB |
-> Seq Scan on pg_namespace nbt (cost=0.00..1.04 rows=4 width=68) (actual time=0.006..0.008 rows=15 loops=1) |
-> Hash (cost=0.00..0.00 rows=1 width=38) (actual time=0.004..0.005 rows=0 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 8kB |
-> Seq Scan on pg_attrdef ad (cost=0.00..0.00 rows=1 width=38) (actual time=0.004..0.004 rows=0 loops=1) |
-> Hash (cost=1.04..1.04 rows=4 width=68) (actual time=0.030..0.030 rows=15 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 10kB |
-> Seq Scan on pg_namespace nt (cost=0.00..1.04 rows=4 width=68) (actual time=0.007..0.010 rows=15 loops=1) |
-> Hash (cost=8.32..8.32 rows=1 width=41) (actual time=0.008..0.009 rows=0 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 8kB |
-> Nested Loop (cost=0.29..8.32 rows=1 width=41) (actual time=0.007..0.008 rows=0 loops=1) |
-> Seq Scan on pg_sequence seq (cost=0.00..0.00 rows=1 width=37) (actual time=0.007..0.007 rows=0 loops=1) |
-> Index Scan using pg_depend_depender_index on pg_depend dep (cost=0.29..8.31 rows=1 width=12) (never executed) |
Index Cond: ((classid = '1259'::oid) AND (objid = seq.seqrelid)) |
Filter: ((deptype = 'i'::"char") AND (refclassid = '1259'::oid)) |
Planning Time: 10.138 ms |
Execution Time: 224.937 ms |
EXPLAIN
QUERY PLAN |
---|
Seq Scan on pg_attribute (cost=0.00..86.25 rows=3025 width=240) (actual time=0.007..0.264 rows=3025 loops=1) |
Planning Time: 0.059 ms |
Execution Time: 0.379 ms |
EXPLAIN