Re: [PERFORM] hashed subplan 5000x slower than two sequential operations

2010-12-08 Thread Bryce Nesbitt
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

Re: [PERFORM] hashed subplan 5000x slower than two sequential operations

2010-12-08 Thread Bryce Nesbitt
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

Re: [PERFORM] hashed subplan 5000x slower than two sequential operations

2010-12-08 Thread Bryce Nesbitt
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

[PERFORM] hashed subplan 5000x slower than two sequential operations

2010-12-08 Thread Bryce Nesbitt
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

Re: [PERFORM] Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-13 Thread Bryce Nesbitt
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

Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-12 Thread Bryce Nesbitt
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

Re: [PERFORM] Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-12 Thread Bryce Nesbitt
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

Re: [PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-12 Thread Bryce Nesbitt
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

Re: [PERFORM] Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-10 Thread Bryce Nesbitt
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

[PERFORM] Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-10 Thread Bryce Nesbitt
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

[PERFORM] 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

2010-02-09 Thread Bryce Nesbitt
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).