Re: [PERFORM] EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Gunnlaugur Thor Briem gunnlau...@gmail.com writes: Yes, I think that's it: I've just realized that immediately prior to the INSERT, in the same transaction, an unfiltered DELETE has been issued; i.e. the whole table is being rewritten. Then the INSERT is issued ... with a WHERE clause on non-existence in the (now empty) table. In that case of course the WHERE clause is unnecessary, as it will always evaluate as true (and we've locked the whole table for writes). Looks like it is a lot worse than unnecessary, though, if it triggers this performance snafu in EXPLAIN INSERT. Ah-hah. So what's happening is that the planner is doing an indexscan over the entire table of now-dead rows, looking vainly for an undeleted maximal row. Ouch. I wonder how hard it would be to make the indexscan give up after hitting N consecutive dead rows, for some suitable N, maybe ~1000. From the planner's viewpoint it'd be easy enough to fall back to using whatever it had in the histogram after all. But that's all happening down inside index_getnext, and I'm hesitant to stick some kind of wart into that machinery for this purpose. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to get explain plan to prefer Hash Join
On Wed, Mar 11, 2015 at 5:35 PM, atxcanadian matthew.bo...@gmail.com wrote: I'm a little perplexed why the autovacuum wasn't keeping up. Any recommendations for those settings to push it to do a bit more analyzing of the tables?? What does pg_stat_user_tables show for that table?
Re: [PERFORM] How to get explain plan to prefer Hash Join
On Thu, Mar 12, 2015 at 8:59 AM, atxcanadian matthew.bo...@gmail.com wrote: Here is the output: http://postgresql.nabble.com/file/n5841610/pg_stat_user_table.jpg This is after I've manually ran analyze. The last_* columns are only showing times, and not full timestamps. Does your reporting tool drop the date part of a timestamp when it is equal to today? Or does it just drop the date part altogether regardless of what it is? Cheers, Jeff
Re: [PERFORM] How to get explain plan to prefer Hash Join
Isn't a random_page_cost of 1 a little aggressive? We are currently setup on Amazon SSD with software RAID 5. -- View this message in context: http://postgresql.nabble.com/How-to-get-explain-plan-to-prefer-Hash-Join-tp5841450p5841605.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to get explain plan to prefer Hash Join
Here is the output: http://postgresql.nabble.com/file/n5841610/pg_stat_user_table.jpg This is after I've manually ran analyze. -- View this message in context: http://postgresql.nabble.com/How-to-get-explain-plan-to-prefer-Hash-Join-tp5841450p5841610.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to get explain plan to prefer Hash Join
Sorry about that, excel clipped off the dates. http://postgresql.nabble.com/file/n5841633/pg_stat_user_table.jpg -- View this message in context: http://postgresql.nabble.com/How-to-get-explain-plan-to-prefer-Hash-Join-tp5841450p5841633.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
