On Apr 25, 2006, at 18:19, Tom Lane wrote:
You'd really have to look at the plans generated for each of the
commands in the functions to be sure. A knee-jerk reaction is to
suggest that that NOT IN might be the core of the problem, but it's
only a guess.
Well, the rows are indexed (I forgot to include the indexes in my
first post), and given that each entry_id has no more than ten
associated tag_ids, I would expect it to be quite fast, relying on
the primary key index to look up the entry_id first, and then the
associated tag_ids. But that's just a guess on my part, too. Perhaps
I should try a left outer join with tag_id IS NULL?
It's a bit tricky to examine the behavior of a parameterized query,
which is what these will all be since they depend on local variables
of the plpgsql function (which are passed as parameters to the main
SQL executor).
Right, that makes sense.
The basic idea is
PREPARE foo(datatype, datatype, ...) AS SELECT ... $1 ... $2 ...
EXPLAIN ANALYZE EXECUTE foo(value, value)
Just on a lark, I tried to get this to work:
try=# explain analyze EXECUTE foo(1, ARRAY
[600001,600002,600003,600004,600005,600006,600007]);
QUERY PLAN
------------------------------------------------------------------------
--------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=26.241..26.251
rows=1 loops=1)
Total runtime: 27.512 ms
(2 rows)
That's not much use. Is there no way to EXPLAIN ANALYZE this stuff?
Thanks Tom.
Best,
David
