From: | Ragnar <gnari(at)hive(dot)is> |
---|---|
To: | Brian Herlihy <btherl(at)yahoo(dot)com(dot)au> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query planner is using wrong index. |
Date: | 2006-04-06 08:52:54 |
Message-ID: | 1144313574.32289.78.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | luchot | 2006-04-06 08:57:41 | Maintenance_work_mem influence on queries |
Previous Message | Leigh Dyer | 2006-04-06 07:42:27 | Re: Sun Fire T2000 and PostgreSQL 8.1.3 |