Marc Mamin wrote:
Another point: would a conditionl index help ?
on articles (context_key) where indexed
no.
production=> select count(*),indexed from articles group by indexed;
count | indexed
+-
517433 | t
695814 | f
--
Sent via pgsql-performance mailing list (pgsql-per
Title: AW: [PERFORM] hashed subplan 5000x slower than two sequential
operations
Marc Mamin wrote:
Hello,
are the table freshly analyzed, with a sufficient
default_statistics_target ?
autovacuum = on # Enable autovacuum
subprocess? 'on'
autovac
Shrirang Chitnis wrote:
Bryce,
The two queries are different:
Ah, due to a mistake. The first version with the hashed subplan is from
production.
The second version should have read:
production=> SELECT c
Can you help me understand how to optimize the following. There's a
subplan which in this case returns 3 rows,
but it is really expensive:
=
explain analyze SELECT contexts.context_key
FROM contexts
JOIN articles
So how about the removal of the "AND" clause? On a test server, this
drops the query from 201204 to 438 ms.
Is this just random, or is it a real solution that might apply to any
arbitrary combination of words?
Attached are three test runs:
Total runtime: 201204.972 ms
Total runtime: 437.766 ms
Tot
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
Tom Lane wrote:
> Given that it estimated 1 row out of "words" (quite correctly) and 12264
> rows out of each scan on article_words, you'd think that the join size
> estimate would be 12264, which would be off by "only" a factor of 3 from
> the true result. Instead it's 23, off by a factor of 200
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
If you guys succeed in making this class of query perform, you'll have
beat out the professional consulting firm we hired, which was all but
useless! The query is usually slow, but particular combinations of
words seem to make it obscenely slow.
The query plans are now attached (sorry I did not s
Or, if you want to actually read that query plan, try:
http://explain.depesz.com/s/qYq
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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
