On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote: > I have a problem with the choice of index made by the query planner. > > My table looks like this: > > CREATE TABLE t > ( > p1 varchar not null, > p2 varchar not null, > p3 varchar not null, > i1 integer, > i2 integer, > i3 integer, > i4 integer, > i5 integer, > d1 date, > d2 date, > d3 date, > PRIMARY KEY (p1, p2, p3) > ); > > I have also created an index on (p2, p3), as some of my lookups are on these > only. > All the integers and dates are data values. > The table has around 9 million rows. > I am using postgresl 7.4.7 > > I have set statistics to 1000 on the p1, p2 and p3 columns, and run vacuum full > analyse. However, I still see > query plans like this: > ... > db=# explain select * from t where p2 = 'fairly_common' and p3 = > 'fairly_common'; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------- > Index Scan using p2p3 on t (cost=0.00..6.01 rows=1 width=102) > Index Cond: (((p2)::text = 'fairly_common'::text) AND ((p3)::text = > 'fairly_common'::text)) > (3 rows) please show us an actual EXPLAIN ANALYZE this will show us more. > I would like the query planner to use the primary key for all of these lookups. > How can I enforce this? How would that help? have you tested to see if it would actualy be better? gnari
