Re: vacuum analyze slows sql query

--- John Meinel <john@johnmeinel.com> wrote:

> If you are trying to establish existence, we also had a whole thread on
> this. Basically what we found was that adding an ORDER BY clause, helped
> tremendously in getting the planner to switch to an Index scan. You
> might try something like:
>
> SELECT column FROM mytable WHERE column='myval' ORDER BY column LIMIT 1;
>
> There seems to be a big difference between the above statement and:
>
> SELECT column FROM mytable WHERE column='myval' LIMIT 1;


The ORDER BY "trick" worked beautifully!  I just hope it'll
continue to work consistently in production code.



> I also wonder about some parts of your query. I don't know your business
> logic but you are tacking a lot of the query into the WHERE, and I
> wonder if postgres just thinks it's going to need to analyze all the
> data before it gets a match.


I have a table of offers (pkk_offer) and a table keeping track
of all purchases against each offer (pkk_purchase) and a third
table keeping track of billing for each purchase (pkk_billing).

That's the basic setup of my db.  In actuallity there are more
tables and views invovled keeping track of usage, etc.

The on UI page that lists all offers in the system needs to
indicated to the user (operator) which offers are "pending".
The term "pending" is used to mean that the particular offer
has either an active purchase against it or has a purchase
which hasn't yet been entered into the billing system yet
(doesn't yet show up in pkk_billing).

An active purcahse is indicated by pkk_purchase.expire_time in
the future or IS NULL.  Where IS NULL indicates a subscription
type purchase (a recurring purchase).  Offers are created either
to be one-time purchasable or subscription type.

The pkk_purchase.pending (boolean) column indicates whether
or not the purchase has been entered into the billing system.
It is a rare case where this flag remains true for a long
period of time (which would indicate something wrong with
the billing sub-system).

There is a foreign key pkk_purchase.offer_id referencing
pkk_offer.offer_id.  And likewise, pkk_billing.client_id
referencing pkk_purchase.client_id and pkk_billing.purchase_id
referecning pkk_purchase.purchase_id.


> So is your function just everything within the CASE statement?

Yes.  I posted a "stripped down" version of the database
on pgsql-sql@ list earlier this month if you are interested
in looking at it:

http://marc.theaimsgroup.com/?l=postgresql-sql&m=109945118928530&w=2

(Just side note: MARC doesn't seem to subscribe to -performance
or -hackers.  I have requested them to carry these two lists. I
think if more people request this it might happen.  I like their
archiving system).


> You might try rewriting it as a loop using a cursor, as I believe using
> a cursor again lends itself to index scans (as it is even more likely
> that you will not get all the data.)

I may try this as well as trying a suggestion by Pierre-Fr���d���ric
Caillaud to use EXISTS, though my initial attempt to use it didn't
seem to be any faster than my original stored function.

So far the ORDER BY "trick" seems to be the best solution.

I appreciate everyone's help and suggestions on this topic!

Best wishes,
--patrick



__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com