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/
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
