I've got two tables, sigs and mags. It's a one-to-one relationship, mags
is just split out because we store a big, less-often-used field
there. "signum" is the key field.
Sometimes I want to know if I have any orphans in mags, so I do a query
like this:
select signum from lp.Mags where signum is not null and signum not
in (select lp.Sigs.signum from lp.Sigs)
(I do this as a subquery because we originally had a old Sybase DB where
outer joins were a non-standard pain and this way works the same and is
DBMS-agnostic.)
At my location, this query runs very fast (~50ms on a ~100k row table)
and 'explain' shows a plan with this structure:
Seq scan on mags
Filter:
SubPlan 1
Seq scan on sigs
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
If I'm reading this correctly, the Materialize is running ~95k times,
taking the majority of the time. Why? The only thing I can think of is
this scenario:
1) server thinks it has a LOT of RAM
2) decides to Materialize subquery to take advantage
3) machine does not actually have that RAM, so it gets swapped
4) server notices it was swapped and decides to re-run rather than
unswap
5) goto 2
I don't know if that's a realistic scenario, but it's all I got. I'm
already well into unknown territory, performance-tuning-wise.
I also decided to try doing the query a different way:
select lp.mags.signum from lp.mags left join lp.sigs on
lp.mags.signum = lp.sigs.signum where lp.mags.signum is not null
and lp.sigs.signum is null;
This one runs fast for both of us. So I guess my second question is: why
can't the query planner tell these are the same query?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
