From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Joseph Shraibman <jks(at)selectacast(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index usage on queries on inherited tables |
Date: | 2011-04-27 20:32:48 |
Message-ID: | BANLkTi=g=dA7Bt_P=m0Amknp9r-k6x5-Sw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, Apr 1, 2011 at 2:41 AM, Joseph Shraibman <jks(at)selectacast(dot)net> wrote:
> 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)
In the first case, PostgreSQL evidently thinks that using the indexes
will be slower than just ignoring them. You could find out whether
it's right by trying it with enable_seqscan=off.
If it turns out that using the indexes really is better, then you
probably want to adjust random_page_cost and seq_page_cost. The
defaults assume a mostly-not-cached database, so if your database is
heavily or completely cached you might need significantly lower
values.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2011-04-27 20:41:35 | Re: Performance |
Previous Message | Robert Haas | 2011-04-27 20:27:48 | Re: Performance |