Search Postgresql Archives

nested query vs left join: query planner very confused

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux