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