Re: [PERFORM] Slow 3 Table Join with v bad row estimate

Sorry Igor - yes wrong plan. Here's the new one ... (running a wee bit slower this morning - still 20x faster that before however) http://explain.depesz.com/s/64YM QUERY PLAN

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

David Osborne writes: > We have 3 different ways we have to do the final X join condition (we use 3 > subqueries UNIONed together), but the one causing the issues is: > (o.branch_code || o.po_number = replace(ss.order_no,' ','')) > ... So we can see straight away that the

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

Thanks very much Tom. Doesn't seem to quite do the trick. I created both those indexes (or the missing one at least) Then I ran analyse on stocksales_ib and branch_purchase_order. I checked there were stats held in pg_stats for both indexes, which there were. But the query plan still predicts 1

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

David Osborne writes: > Doesn't seem to quite do the trick. I created both those indexes (or the > missing one at least) > Then I ran analyse on stocksales_ib and branch_purchase_order. > I checked there were stats held in pg_stats for both indexes, which there > were. > But

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

Ok - wow. Adding that index, I get the same estimate of 1 row, but a runtime of ~450ms. A 23000ms improvement. http://explain.depesz.com/s/TzF8h This is great. So as a general rule of thumb, if I see a Join Filter removing an excessive number of rows, I can check if that condition can be added

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of David Osborne Sent: Tuesday, November 10, 2015 12:32 PM To: Tom Lane <t...@sss.pgh.pa.us> Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow 3 Table Join with v b