index usage on queries on inherited tables

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: index usage on queries on inherited tables
Date: 2011-04-01 00:41:01
Message-ID: in36ut$24hs$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

When I do a query on a table with child tables on certain queries pg
uses indexes and on others it doesn't. Why does this happen? For example:

[local]:playpen=> explain analyze select * from vis where id > 10747 ;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Result (cost=4.29..115.11 rows=325 width=634) (actual
time=0.063..0.116 rows=5 loops=1)
-> Append (cost=4.29..115.11 rows=325 width=634) (actual
time=0.053..0.090 rows=5 loops=1)
-> Bitmap Heap Scan on vis (cost=4.29..23.11 rows=5
width=948) (actual time=0.051..0.058 rows=5 loops=1)
Recheck Cond: (id > 10747)
-> Bitmap Index Scan on vis_pkey (cost=0.00..4.29
rows=5 width=0) (actual time=0.037..0.037 rows=5 loops=1)
Index Cond: (id > 10747)
-> Seq Scan on vis_for_seg_1_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_4_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_66_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_69_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_79_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_80_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_82_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (id > 10747)
-> Seq Scan on vis_for_seg_87_2011_03 vis (cost=0.00..11.50
rows=40 width=629) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (id > 10747)
Total runtime: 0.724 ms
(23 rows)

Time: 5.804 ms
[local]:playpen=> explain analyze select * from vis where id = 10747 ;

QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..74.41 rows=9 width=664) (actual time=0.060..0.503
rows=1 loops=1)
-> Append (cost=0.00..74.41 rows=9 width=664) (actual
time=0.053..0.493 rows=1 loops=1)
-> Index Scan using vis_pkey on vis (cost=0.00..8.27 rows=1
width=948) (actual time=0.051..0.055 rows=1 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_1_2011_03_pkey on
vis_for_seg_1_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.122..0.122 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_4_2011_03_pkey on
vis_for_seg_4_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.043..0.043 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_66_2011_03_pkey on
vis_for_seg_66_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.041..0.041 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_69_2011_03_pkey on
vis_for_seg_69_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.041..0.041 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_79_2011_03_pkey on
vis_for_seg_79_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.043..0.043 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_80_2011_03_pkey on
vis_for_seg_80_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.041..0.041 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_82_2011_03_pkey on
vis_for_seg_82_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.049..0.049 rows=0 loops=1)
Index Cond: (id = 10747)
-> Index Scan using vis_for_seg_87_2011_03_pkey on
vis_for_seg_87_2011_03 vis (cost=0.00..8.27 rows=1 width=629) (actual
time=0.043..0.043 rows=0 loops=1)
Index Cond: (id = 10747)
Total runtime: 1.110 ms
(21 rows)

[local]:playpen=> select version();

version
------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-48), 32-bit
(1 row)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joseph Shraibman 2011-04-01 00:41:04 table contraints checks only happen in planner phase
Previous Message Jeremy Palmer 2011-03-31 19:43:30 Re: Slow deleting tables with foreign keys