--- John Meinel <john@xxxxxxxxxxxxxx> 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
