On Sat, Feb 13, 2010 at 2:58 AM, Bryce Nesbitt 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 arbitrary combinati
Your Query :
SELECT contexts.context_key FROM contexts
JOIN articles ON (articles.context_key=contexts.context_key)
JOIN matview_82034 ON (contexts.context_key=matview_82034.context_key)
WHERE contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key)
WHERE
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 th
Kevin Grittner wrote:
Bryce Nesbitt 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 a
Karl Denninger 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 instantly and the
Karl Denninger 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? Coul
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
forum=p
Karl Denninger 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 this o
"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
should
Bryce Nesbitt 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 (articles.context_key = contexts.con
I've got a very slow query, which I can make faster by doing something
seemingly trivial.
The query has been trouble for years (always slow, sometimes taking hours):
512,600ms Original, filter on articles.indexed (622 results)
7,500ms Remove "AND articles.indexed" (726 results, undesirable).
11 matches
Mail list logo
