Re: Non-unique index performance

On Fri, Jun 24, 2005 at 11:44:50AM +0300, Sezai YILMAZ wrote:
> Hello!
>
> I have a table eith name person as described below. It has an unique
> index for id column (it is also primary key) and has an index for parent
> column.

<snip>

> Why the difference of both queries is so dramatical for unique and
> non-unique indexed columns? Why PostgreSQL does not use the non-unique
> indexes (it says that it does sequential scan)?

<snip>

It has nothing to do with the index and everything to do with how many
rows it expected to return. If you look at the explain output you'll
see that the first only expected 5 rows to be returned, whereas the
second expected 14427 rows. Looking up 14000 rows in a index is rather
expensive so PostgreSQL decided that a seq scan would be faster.

If these numbers arn't accurate, you need to show EXPLAIN ANALYZE
output as well as checking how often you run ANALYZE generally.

Hope this helps,

> test=> explain analyze select id,name from person where id in ('17201',
> '338191', '244319', '515209', '20415');
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using person_pkey, person_pkey, person_pkey, person_pkey,
> person_pkey on person (cost=0.00..30.12 rows=5 width=18) (actual
> time=56.817..225.760 rows=5 loops=1)
> Index Cond: ((id = 17201) OR (id = 338191) OR (id = 244319) OR (id =
> 515209)OR (id = 20415))
> Total runtime: 225.893 ms
> (3 rows)
>
>
>
>
> test=> explain analyze select * from person where parent in ('17201',
> '338191', '244319', '515209', '20415');
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Seq Scan on person (cost=0.00..35871.26 rows=14427 width=32) (actual
> time=0.063..11192.809 rows=5 loops=1)
> Filter: ((parent = 17201) OR (parent = 338191) OR (parent = 244319)
> OR (parent = 515209) OR (parent = 20415))
> Total runtime: 11192.913 ms
> (3 rows)
>

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.