Re: Query planner is using wrong index.

[Ragnar <gnari(at)hive(dot)is>]

On fös, 2006-04-07 at 00:01 +1000, Brian Herlihy wrote:
> --- Ragnar <gnari(at)hive(dot)is> wrote:
> 
> > On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote:
> > 
> > > Yes, the primary key is far better.  I gave it the ultimate test - I
> > dropped
> > > the (p2, p3) index.  It's blindingly fast when using the PK, 
> > 
> > I have problems understanding exactly how an index on 
> > (p1,p2,p3) can be faster than and index on (p2,p3) for
> > a query not involving p1.

> db# explain analyze select * from t WHERE p1 = 'a' and p2 = 'uk.altavista.com'
> AND p3 = 'web/results?itag=&q=&kgs=&kls=';

this is different from what you said earlier. in your 
original post you showed a problem query without any
reference to p1 in the WHERE clause. this confused me.

>  Index Scan using p2_p3_idx on t  (cost=0.00..6.02 rows=1 width=102) (actual
> time=2793.247..2793.247 rows=0 loops=1)
>    Index Cond: (((p2)::text = 'uk.altavista.com'::text) AND ((p3)::text =
> 'web/results?itag=&q=&kgs=&kls='::text))
>    Filter: ((p1)::text = 'a'::text)
>  Total runtime: 2793.303 ms
> (4 rows)

try to add an ORDER BY clause:

explain analyze 
  select * from t 
  WHERE p1 = 'a'
    and p2 = 'uk.altavista.com'
    AND p3 = 'web/results?itag=&q=&kgs=&kls='
  ORDER BY p1,p2,p3;

this might push the planner into using the primary key

gnari