agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Chris <dmagick@gmail.com>
To: Jonathan Gray <jgray@streamy.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance issue
Date: Tue, 24 Jul 2007 19:36:25 +1000
Message-ID: <46A5C819.80707@gmail.com> (raw)
In-Reply-To: <0f3001c7cdd3$a7db7740$f79265c0$@com>
References: <0f1701c7cdc6$f9cc1a30$ed644e90$@com>
<46A5BBD4.8040509@gmail.com>
<46A5BD6F.7000108@gmail.com>
<0f3001c7cdd3$a7db7740$f79265c0$@com>
Jonathan Gray wrote:
> Chris,
>
> Creating indexes on the customerclass table does speed up the queries but
> still does not create the plan we are looking for (using the double index
> with a backward index scan on the orders table).
Stupid question - why is that particular plan your "goal" plan?
> The plans we now get, with times on par or slightly better than with the
> plpgsql hack, are:
>
> EXPLAIN ANALYZE
> SELECT o.orderid,o.orderstamp FROM indextest.orders o
> INNER JOIN indextest.customerclass cc ON (cc.classid = 2)
> WHERE o.customerid = cc.customerid ORDER BY o.orderstamp DESC LIMIT 5;
Didn't notice this before...
Shouldn't this be:
INNER JOIN indextest.customerclass cc ON (o.customerid = cc.customerid)
WHERE cc.classid = 2
ie join on the common field not the classid one which doesn't appear in
the 2nd table?
> As I said, this is a hypothetical test case we have arrived at that
> describes our situation as best as we can given a simple case. We're
> interested in potential issues with the approach, why postgres would not
> attempt something like it, and how we might go about implementing it
> ourselves at a lower level than we currently have (in SPI, libpq, etc).
>
> If it could be generalized then we could use it in cases where we aren't
> pulling from just one table (the orders table) but rather trying to merge,
> in sorted order, results from different conditions on different tables.
> Right now we use something like the plpgsql or plpythonu functions in the
> example and they outperform our regular SQL queries by a fairly significant
> margin.
I'm sure if you posted the queries you are running with relevant info
you'd get some help ;)
--
Postgresql & php tutorials
http://www.designmagick.com/
view thread (57+ messages) latest in thread
Message-ID: <46A5C819.80707@gmail.com>
Permalink: ../46A5C819.80707@gmail.com/
Also on: postgresql.org/message-id/46A5C819.80707@gmail.com
reply
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: dmagick@gmail.com, jgray@streamy.com
Subject: Re: Query performance issue
In-Reply-To: <46A5C819.80707@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox