From: Ragnar <gnari@hive.is>
To: Brian Herlihy <btherl@yahoo.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query planner is using wrong index.
Date: Thu, 06 Apr 2006 16:20:29 +0000
Message-ID: <1144340430.32289.97.camel@localhost.localdomain> (raw)
In-Reply-To: <20060406140151.83127.qmail@web52310.mail.yahoo.com>
References: <20060406140151.83127.qmail@web52310.mail.yahoo.com>
On fös, 2006-04-07 at 00:01 +1000, Brian Herlihy wrote:
> --- Ragnar <gnari@hive.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnari@hive.is, btherl@yahoo.com.au
Subject: Re: Query planner is using wrong index.
In-Reply-To: <1144340430.32289.97.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox