Re: Help with nested loop left join performance - Mailing list pgsql-performance
From | Richard Huxton |
---|---|
Subject | Re: Help with nested loop left join performance |
Date | |
Msg-id | 43FC30FC.3080800@archonet.com Whole thread Raw |
In response to | Help with nested loop left join performance ("George Woodring" <george.woodring@iglass.net>) |
Responses |
Re: Help with nested loop left join performance
|
List | pgsql-performance |
George Woodring wrote: > > explain analyze SELECT column1, column2, column3, column4, column5, > column6, column7, column8 FROM (SELECT CASE status WHEN 0 THEN 0 WHEN 1 > THEN 1 ELSE -1 END AS column1, mac AS column2, account AS column3, > number || ' ' || address AS column4, 'qmod' || '.' || 'dmod' AS column5, > node AS column6, grid AS column7, boxtype AS column8, number, address > FROM settop_billing LEFT OUTER JOIN (dhct JOIN dhct_davic USING(mac)) > USING (mac) WHERE region='GTown1E' AND node='1E012' ) AS foo; Ach y fi! Let's format that a bit better, eh? explain analyze SELECT column1, column2, column3, column4, column5,column6, column7, column8 FROM ( SELECT CASE status WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END AS column1, mac AS column2, account AS column3, number || ' ' || address AS column4, 'qmod' || '.' || 'dmod' AS column5, node AS column6, grid AS column7, boxtype AS column8, number, address FROM settop_billing LEFT OUTER JOIN (dhct JOIN dhct_davic USING(mac)) USING (mac) WHERE region='GTown1E' AND node='1E012' ) AS foo; Now we can see what's happening. Well, looking at it laid out like that, I'm suspcious of the (dhct JOIN dhct_davic) on the outside of an outer join. Looking at your explain we do indeed have two sequential scans over the tables in question - the big one being dhct... > -> Append (cost=0.00..8881.11 rows=384912 width=8) > (actual time=0.023..10914.365 rows=384900 loops=1) > -> Seq Scan on dhct_davic (cost=0.00..0.00 rows=1 > width=8) (actual time=0.002..0.002 rows=0 loops=1) > -> Seq Scan on probe_dhct_davic dhct_davic > (cost=0.00..8881.11 rows=384911 width=8) (actual time=0.018..10505.255 > rows=384900 loops=1) > -> Hash (cost=12154.13..12154.13 rows=410613 width=6) > (actual time=923.433..923.433 rows=0 loops=1) > -> Seq Scan on dhct (cost=0.00..12154.13 > rows=410613 width=6) (actual time=0.019..534.641 rows=409576 loops=1) With 7.4 I seem to remember that explicit JOINs force the evaluation order, but I'm not if even later versions will rewrite your query. It's too early in the morning for me to figure out if it's safe in all cases. Anyway, for your purposes, I'd say something more like: FROM settop_billing LEFT JOIN dhct LEFT JOIN dhct_davic That should let the planner do the joins in a more reasonable order. -- Richard Huxton Archonet Ltd
pgsql-performance by date: