On Wed, Nov 27, 2013 at 7:56 AM, David Rysdam <drys...@ll.mit.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?
