Skip site navigation (1) Skip section navigation (2)

Re: Selective usage of index in planner/optimizer (Too conservative?)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ludwig Lim <lud_nowhere_man(at)yahoo(dot)com>
Cc: PostgreSQL Mailing List <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Selective usage of index in planner/optimizer (Too conservative?)
Date: 2002-10-22 14:24:24
Message-ID: 18687.1035296664@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Ludwig Lim <lud_nowhere_man(at)yahoo(dot)com> writes:
> NOTICE:  QUERY PLAN:

> Aggregate  (cost=119123.54..119123.54 rows=1 width=0)
> (actual time=811.08..811.0
> 8 rows=1 loops=1)
>   ->  Index Scan using idx_monthly_branch on points 
> (cost=0.00..1187
> 65.86 rows=143073 width=0) (actual time=0.19..689.75
> rows=136790 loops=1)
> Total runtime: 811.17 msec

> NOTICE:  QUERY PLAN:

> Aggregate  (cost=62752.34..62752.34 rows=1 width=0)
> (actual time=3593.93..3593.9
> 3 rows=1 loops=1)
>   ->  Seq Scan on points  (cost=0.00..62681.70
> rows=28254 width=0) (a
> ctual time=0.33..3471.54 rows=136790 loops=1)
> Total runtime: 3594.01 msec

Something fishy about this --- why is the estimated number of rows
different in the two cases (143073 vs 28254)?  Did you redo VACUUM
and/or ANALYZE in between?

>    I am wondering why in test case #2 it did not use
> an index scan, where as in case #3 it did.

Probably because it knows "branch_cd=5" is more selective than
"branch_cd=1".  It would be useful to see the pg_stats entry for
branch_cd.

>    Its rather strange why "SELECT COUNT(*)...WHERE
> branch_cd=1" uses sequential scan even though it just
> comprises 5.3% of whole table...

No, what's strange is that it's faster to use an indexscan for that.
The table must be very nearly in order by branch_cd; have you clustered
it recently?

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Ludwig LimDate: 2002-10-23 01:48:04
Subject: Re: Selective usage of index in planner/optimizer (Too conservative?)
Previous:From: Ludwig LimDate: 2002-10-22 11:47:38
Subject: Selective usage of index in planner/optimizer (Too conservative?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group