Re: A Better Way? (Multi-Left Join Lookup)

"David Johnston" <polobo(at)yahoo(dot)com> writes:
>> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>> Surely not. Neither merge nor hash joins require an index. What plan is
>> getting selected?

> I have attached a scrubbed query and explain/analyze. Let me know if
> something more is needed.

Well, here's your problem:

> CTE master_listing {# The LEFT side of the multi-joins #}
> -> Subquery Scan on call (cost=22762.65..22762.94 rows=1 width=32) (actual time=619.158..735.559 rows=8656 loops=1)

The planner thinks master_listing will return only one row, which would
make a nestloop the right way to do things. However, with 8500 rows
coming out, the nestloop iterates 8500 times and takes forever.

So what you need to do is figure out why that rowcount estimate is so
far off and do whatever's needful to make it better. It does not have
to be dead on --- even an estimate of a few dozen rows would likely be
enough to discourage the planner from using a nestloop.

You haven't shown enough info for anybody else to guess exactly why
the rowcount estimate is bad, though.

regards, tom lane