Re: nested query vs left join: query planner very confused

On Wed, Nov 27, 2013 at 7:56 AM, David Rysdam <drysdam(at)ll(dot)mit(dot)edu> wrote:

>
>
> At my client's location, the query is very slow (same table size,
> similar hardware/config, although they are running 9.0.x and I'm on
> 9.2.x). By "slow" I mean like an *hour*. 'explain' has this structure:
>
> Seq scan on mags
> Filter:
> SubPlan 1
> Materialize
> Seq scan on sigs
>
> I'd never heard of Materialize before, so I looked into it. Seems to
> make a virtual table of the subquery so repetitions of the parent query
> don't have to re-do the work. Sounds like it should only help, right?
>
> The client's 'explain analyze' shows this:
>
> Seq Scan on mags (cost=0.00..187700750.56 rows=47476 width=4)
> (actual time=3004851.889..3004851.889 rows=0 loops=1)
> Filter: ((signum IS NOT NULL) AND (NOT (SubPlan 1)))
> SubPlan 1
> -> Materialize (cost=0.00..3713.93 rows=95862 width=4)
> (actual time=0.011..16.145 rows=48139 loops=94951)
> -> Seq Scan on sigs (cost=0.00..2906.62
> rows=95862 width=4) (actual time=0.010..674.201 rows=95862 loops=1)
> Total runtime: 3004852.005 ms
>

Has the client ANALYZEd recently? What happens if the client issues the
following commands before executing the query?
VACUUM ANALYZE lp.sigs;
VACUUM ANALYZE lp.mags;

If that doesn't change the plan, could you post the values for
effective_cache_size, shared_buffers, random_page_cost, cpu_tuple_cost,
work_mem and how much RAM is in the client machine?