Re: [PERFORM] How to get explain plan to prefer Hash Join

2015-03-11 Thread Pavel Stehule
2015-03-12 1:35 GMT+01:00 atxcanadian : > So I implemented two changes. > > - Moved random_page_cost from 1.1 to 2.0 > random_page_cost 1 can enforce nested_loop - it is very cheap with it > - Manually ran analyze on all the tables > > *Here is the new explain analyze:* > QUERY PLAN > HashAggre

Re: [PERFORM] How to get explain plan to prefer Hash Join

2015-03-11 Thread atxcanadian
So I implemented two changes. - Moved random_page_cost from 1.1 to 2.0 - Manually ran analyze on all the tables *Here is the new explain analyze:* QUERY PLAN HashAggregate (cost=74122.97..74125.53 rows=256 width=24) (actual time=45.205..45.211 rows=24 loops=1) InitPlan 1 (returns $0) ->

Re: [PERFORM] How to get explain plan to prefer Hash Join

2015-03-11 Thread Jeff Janes
On Wed, Mar 11, 2015 at 10:01 AM, atxcanadian wrote: > Currently seeing massive increase in performance when optimizer chooses > Hash > Join over Nested Loops. I achieve this by temporarily setting nested loops > off. I'd like to setup some database variables where the optimizer prefers > hash jo

Re: [PERFORM] How to get explain plan to prefer Hash Join

2015-03-11 Thread Nicholson, Brad (Toronto, ON, CA)
> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of atxcanadian > Sent: Wednesday, March 11, 2015 1:01 PM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] How to get explain plan to prefer Hash Join >

Re: [PERFORM] EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

2015-03-11 Thread Tomas Vondra
On 11.3.2015 18:30, Jeff Janes wrote: > On Sat, Mar 7, 2015 at 7:44 AM, Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > On 7.3.2015 03:26, Jeff Janes wrote: > > On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane > >

Re: [PERFORM] EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

2015-03-11 Thread Jeff Janes
On Sat, Mar 7, 2015 at 7:44 AM, Tomas Vondra wrote: > On 7.3.2015 03:26, Jeff Janes wrote: > > On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane > > wrote: > > > > But the actual query is using a seq scan, and so it would hint the > > table in efficient sequential order, rather

[PERFORM] How to get explain plan to prefer Hash Join

2015-03-11 Thread atxcanadian
Currently seeing massive increase in performance when optimizer chooses Hash Join over Nested Loops. I achieve this by temporarily setting nested loops off. I'd like to setup some database variables where the optimizer prefers hash joins. Any suggestions? *Query in question:* explain analyze selec

Re: [PERFORM] EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

2015-03-11 Thread Gunnlaugur Thor Briem
On Sat, Mar 7, 2015 at 3:44 PM, Tomas Vondra wrote: > Another possibility is that this is part > of some large batch, and autovacuum simply did not have change to do the > work. > Yes, I think that's it: I've just realized that immediately prior to the INSERT, in the same transaction, an unfilte