Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 753661337BC8 for ; Thu, 31 Mar 2011 21:53:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 80773-07 for ; Fri, 1 Apr 2011 00:53:02 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id F36B21337999 for ; Thu, 31 Mar 2011 21:53:00 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.4/8.14.4) with ESMTP id p310r0aW000961 for ; Thu, 31 Mar 2011 21:53:00 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.4/8.14.4/Submit) id p310f10H070270 for pgsql-performance@postgresql.org; Thu, 31 Mar 2011 21:41:01 -0300 (ADT) (envelope-from news) From: Joseph Shraibman X-Newsgroups: pgsql.performance Subject: index usage on queries on inherited tables Date: Thu, 31 Mar 2011 20:41:01 -0400 Organization: Hub.Org Networking Services Lines: 102 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.15) Gecko/20110307 Fedora/3.1.9-0.38.b3pre.fc13 Thunderbird/3.1.9 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/382 X-Sequence-Number: 43061 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)