Non-unique index performance - Mailing list pgsql-general
From | Sezai YILMAZ |
---|---|
Subject | Non-unique index performance |
Date | |
Msg-id | 42BBC802.2030601@pro-g.com.tr Whole thread Raw |
Responses |
Re: Non-unique index performance
(Richard Huxton <dev@archonet.com>)
Re: Non-unique index performance (Martijn van Oosterhout <kleptog@svana.org>) |
List | pgsql-general |
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. If I run a query with where clause on id column it uses the index (look at the first explain analyze result; it says "Index Scan using...") and the query for 582856 rows table results in 225,893 ms. But, if I run another query with where clause on parent column it does not use the index (look at the second explain analyze result; it says "Seq Scan using...") and the query for 582856 rows table results in 11192.913 ms. 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)? I have to use an index on non-unique column. What is the solution for that? Is there a way to speed up non-unique indexes? ***************************************************************** test=> \d person Table "public.person" Column | Type | Modifiers ---------+-----------------------+----------- name | character varying(30) | surname | character varying(30) | id | integer | not null parent | integer | Indexes: "person_pkey" primary key, btree (id) "parent_ndx" btree (parent) 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) ***************************************************************** Thanks! -- sy
pgsql-general by date: