Re: hash semi join caused by "IN (select ...)"

On Mon, May 16, 2011 at 3:30 PM, Clemens Eisserer <linuxhippy(at)gmail(dot)com>wrote:

> Hi,
>
> I have a quite complex, performance sensitive query in a system with a
> few (7) joins:
> select .... from t1 left join t2 .... WHERE id IN (select ....)
>
> For this query the planner evaluates the IN with a hash semi join last,
> and all the joining is done by hash joins for all rows contained in t1.
>
> However when I specify the ids manually (IN (1, 2, 3, 4, 5) the
> planner first does an index lookup on the primary key column id,
> and subsequently does nested loop joins using an index on t2 - which
> gives way better results.
>
> Is there any way to guide the planner to evaluate the IN condition
> first, instead of last?
> Why is the planner behaving this way? (postgresql 8.4.??)
>
> Thank you in advance, Clemens
>
>
> Query plan with IN(select):
>
> Sort (cost=165.77..165.77 rows=2 width=16974) (actual
> time=13.459..13.460 rows=2 loops=1)
> Sort Key: this_.id
> Sort Method: quicksort Memory: 26kB
> -> Hash Semi Join (cost=123.09..165.76 rows=2 width=16974)
> (actual time=12.741..13.432 rows=2 loops=1)
> Hash Cond: (this_.id = kladdenent0_.id)
> -> Hash Left Join (cost=119.17..160.90 rows=348
> width=16974) (actual time=8.765..13.104 rows=342 loops=1)
> Hash Cond: (flugzeug2_.flugzeugtyp_id = flugzeugty3_.id)
> -> Hash Left Join (cost=118.10..155.08 rows=348
> width=16454) (actual time=8.724..12.412 rows=342 loops=1)
> Hash Cond: (flugzeug2_.zaehlertyp_id = bmintype4_.id)
> -> Hash Left Join (cost=117.06..152.71 rows=348
> width=15934) (actual time=8.660..11.786 rows=342 loops=1)
> Hash Cond: (this_.lehrerid = pilot5_.id)
> -> Hash Left Join (cost=96.66..130.46
> rows=348 width=8912) (actual time=6.395..8.899 rows=342 loops=1)
> Hash Cond: (this_.nachid = flugplatz6_.id)
> -> Hash Left Join
> (cost=93.89..122.90 rows=348 width=8370) (actual time=6.354..8.429
> rows=342 loops=1)
> Hash Cond: (this_.flugzeugid =
> flugzeug2_.id)
> -> Hash Left Join
> (cost=23.17..47.04 rows=348 width=7681) (actual time=1.992..3.374
> rows=342 loops=1)
> Hash Cond: (this_.pilotid
> = pilot7_.id)
> -> Hash Left Join
> (cost=2.78..22.04 rows=348 width=659) (actual time=0.044..0.548
> rows=342 loops=1)
> Hash Cond:
> (this_.vonid = flugplatz8_.id)
> -> Seq Scan on
> startkladde this_ (cost=0.00..14.48 rows=348 width=117) (actual
> time=0.004..0.074 rows=342 loops=1)
> -> Hash
> (cost=1.79..1.79 rows=79 width=542) (actual time=0.032..0.032 rows=79
> loops=1)
> -> Seq Scan
> on flugplatz flugplatz8_ (cost=0.00..1.79 rows=79 width=542) (actual
> time=0.003..0.010 rows=79 loops=1)
> -> Hash
> (cost=15.73..15.73 rows=373 width=7022) (actual time=1.938..1.938
> rows=375 loops=1)
> -> Seq Scan on
> pilot pilot7_ (cost=0.00..15.73 rows=373 width=7022) (actual
> time=0.006..0.769 rows=375 loops=1)
> -> Hash (cost=51.43..51.43
> rows=1543 width=689) (actual time=4.351..4.351 rows=1543 loops=1)
> -> Seq Scan on flugzeug
> flugzeug2_ (cost=0.00..51.43 rows=1543 width=689) (actual
> time=0.006..1.615 rows=1543 loops=1)
> -> Hash (cost=1.79..1.79 rows=79
> width=542) (actual time=0.031..0.031 rows=79 loops=1)
> -> Seq Scan on flugplatz
> flugplatz6_ (cost=0.00..1.79 rows=79 width=542) (actual
> time=0.003..0.011 rows=79 loops=1)
> -> Hash (cost=15.73..15.73 rows=373
> width=7022) (actual time=2.236..2.236 rows=375 loops=1)
> -> Seq Scan on pilot pilot5_
> (cost=0.00..15.73 rows=373 width=7022) (actual time=0.005..0.781
> rows=375 loops=1)
> -> Hash (cost=1.02..1.02 rows=2 width=520)
> (actual time=0.005..0.005 rows=2 loops=1)
> -> Seq Scan on bmintype bmintype4_
> (cost=0.00..1.02 rows=2 width=520) (actual time=0.003..0.004 rows=2
> loops=1)
> -> Hash (cost=1.03..1.03 rows=3 width=520) (actual
> time=0.004..0.004 rows=3 loops=1)
> -> Seq Scan on flugzeugtype flugzeugty3_
> (cost=0.00..1.03 rows=3 width=520) (actual time=0.002..0.002 rows=3
> loops=1)
> -> Hash (cost=3.90..3.90 rows=2 width=4) (actual
> time=0.239..0.239 rows=2 loops=1)
> -> Limit (cost=0.00..3.88 rows=2 width=4) (actual
> time=0.202..0.236 rows=2 loops=1)
> -> Index Scan using startkladde_pkey on
> startkladde kladdenent0_ (cost=0.00..56.24 rows=29 width=4) (actual
> time=0.200..0.233 rows=2 loops=1)
> Filter: ((status > 0) OR (id = (-1)))
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In some cases, I've seen improved results when replacing the IN (...) with =
ANY(ARRAY(...)).
Dave