On Sat, Feb 13, 2010 at 2:58 AM, Bryce Nesbitt bry...@obviously.com wrote:
So as the op, back to the original posting
In the real world, what should I do? Does it make sense to pull the AND
articles.indexed clause into an outer query? Will that query simply
perform poorly on other
Bryce Nesbitt bry...@obviously.com wrote:
I've got a very slow query, which I can make faster by doing
something seemingly trivial.
Out of curiosity, what kind of performance do you get with?:
EXPLAIN ANALYZE
SELECT contexts.context_key
FROM contexts
JOIN articles ON
Exists can be quite slow. So can not exists
See if you can re-write it using a sub-select - just replace the exists
with (select ...) is not null
Surprisingly this often results in a MUCH better query plan under
Postgresql. Why the planner evaluates it better eludes me (it
shouldn't) but
Karl Denninger k...@denninger.net wrote:
Kevin Grittner wrote:
I suspect that the above might do pretty well in 8.4.
Exists can be quite slow. So can not exists
See if you can re-write it using a sub-select - just replace the
exists with (select ...) is not null
Surprisingly
Yes:
select forum, * from post where
marked is not true
and toppost = 1
and (select login from ignore_thread where login='xxx' and
number=post.number) is null
and (replied now() - '30 days'::interval)
and (replied (select lastview from forumlog where login='xxx' and
Karl Denninger k...@denninger.net wrote:
Kevin Grittner wrote:
Have you seen such a difference under 8.4? Can you provide a
self-contained example?
Yes:
[query and EXPLAIN ANALYZE of fast query]
The alternative:
[query with no other information]
goes nuts.
Which means what?
Karl Denninger k...@denninger.net writes:
Explain Analyze on the alternative CLAIMS the same query planner time
(within a few milliseconds) with explain analyze. But if I replace the
executing code with one that has the alternative (not exists) syntax
in it, the system load goes to crap
Kevin Grittner wrote:
Bryce Nesbitt bry...@obviously.com wrote:
I've got a very slow query, which I can make faster by doing
something seemingly trivial.
Out of curiosity, what kind of performance do you get with?:
EXPLAIN ANALYZE
SELECT contexts.context_key
So as the op, back to the original posting
In the real world, what should I do? Does it make sense to pull the
"AND articles.indexed" clause into an outer query? Will that query
simply perform poorly on other arbitrary combinations of words?
I'm happy to test any given query against
