patrick ~ wrote:
--- John Meinel [EMAIL PROTECTED] 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
Sorry for the late reply. Was feeling a bit under the weather
this weekend and didn't get a chance to look at this.
--- Tom Lane [EMAIL PROTECTED] wrote:
patrick ~ [EMAIL PROTECTED] writes:
PREPARE pkk_00 ( integer ) the def of pkk_offer_has_pending_purc( integer
)
This is what you
patrick ~ wrote:
[...]
pkk=# explain analyze execute pkk_01(241 );
QUERY PLAN
-
Result
Hi John,
Thanks for your reply and analysis.
--- John Meinel [EMAIL PROTECTED] wrote:
patrick ~ wrote:
[...]
pkk=# explain analyze execute pkk_01(241 );
QUERY PLAN
patrick ~ wrote:
Hi John,
Thanks for your reply and analysis.
No problem. It just happens that this is a problem we ran into recently.
--- John Meinel [EMAIL PROTECTED] wrote:
patrick ~ wrote:
[...]
Hmm... The fact is I am selecting (in this example anyway) over all
values in pkk_offer table and
Lets say for a second that you manage to trick it into using index scan,
and then you actually call the function with one of the values that
returns 1,000s of rows. Probably it will take 10-100 times longer than
if it used a seq scan.
I don't know if it matters (I suspect that it does) but I am
patrick ~ wrote:
Hi John,
Thanks for your reply and analysis.
--- John Meinel [EMAIL PROTECTED] wrote:
patrick ~ wrote:
[...]
pkk=# explain analyze execute pkk_01(241 );
QUERY PLAN
One other thing that I just thought of. I think it is actually possible
to add an index on a function of
Just wanted to know if there were any insights after looking at
requested 'explain analyze select ...'?
Thanks,
--patrick
__
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
---(end of
Looking around at the pg_ tables and some PostgreSQL online
docs prompted by another post/reply on this list regarding
ALERT TABLE SET STATISTICS i found out that prior to a VACUUM
the following select (taken from the online docs) shows:
pkk=# select relname, relkind, reltuples, relpages from
patrick ~ [EMAIL PROTECTED] writes:
1. Is this really the only solution left for me?
You still haven't followed the suggestions that were given to you
(ie, find out what is happening with the plan for the query inside
the problematic function).
regards, tom lane
Hi Tom, -performance@,
I apologize if I didn't follow through with the PREPARE and
EXECUTE. I assume that is what you are refering to. After
reading the PostgreSQL docs on PREPARE statement I realized
two things: a) PREPARE is only session long and b) that I
can not (at least I haven't figured
patrick ~ [EMAIL PROTECTED] writes:
PREPARE pkk_00 ( integer ) the def of pkk_offer_has_pending_purc( integer )
This is what you want to do, but not quite like that. The PREPARE
determines the plan and so VACUUMing and re-EXECUTing is going to show
the same plan. What we need to look at is
Greetings pgsql-performance :)
Yesterday I posted to the pgsql-sql list about an issue with VACUUM
while trying to track-down an issue with performance of a SQL SELECT
statement invovling a stored function. It was suggested that I bring
the discussion over to -performance.
Instread of reposting
Given that the plan doesn't change after an analyze, my guess would be that the first query is hitting cached data, then
you vacuum and that chews though all the cache with its own data pushing the good data out of the cache so it has to
be re-fetched from disk.
If you run the select a 2nd
patrick ~ [EMAIL PROTECTED] writes:
that if I 'createdb' and populate it with the sanatized data the
query in question is quite fast; 618 rows returned in 864.522 ms.
This was puzzling. Next I noticed that after a VACUUM the very same
query would slow down to a crawl; 618 rows returned in
Here is a fresh run with 'explain analyze' run before and after the
VACUUM statement:
-- begin
% dropdb pkk
DROP DATABASE
% createdb pkk
CREATE DATABASE
% psql pkk pkk_db.sql
ERROR: function pkk_offer_has_pending_purch(integer) does not exist
ERROR: function
16 matches
Mail list logo
