Re: performance of IN (subquery)

Kevin Murphy wrote:

> ------------------------------------------------------------------------
> -------------------------------------------------------------
> Hash Join (cost=70.33..72.86 rows=25 width=4) (actual
> time=583.051..583.059 rows=2 loops=1)
> Hash Cond: ("outer".element_id = "inner".elementid)
> -> HashAggregate (cost=47.83..47.83 rows=25 width=4) (actual
> time=0.656..0.658 rows=2 loops=1)
> -> Hash Join (cost=22.51..47.76 rows=25 width=4) (actual
> time=0.615..0.625 rows=2 loops=1)
> Hash Cond: ("outer".superloc_id = "inner".superloc_id)
> -> Seq Scan on superlocs_2 (cost=0.00..20.00 rows=1000 width=8)
> (actual time=0.004..0.012 rows=9 loops=1)
> -> Hash (cost=22.50..22.50 rows=5 width=4) (actual time=0.076..0.076
> rows=0 loops=1)
> -> Seq Scan on bundle_superlocs_2 (cost=0.00..22.50 rows=5 width=4)
> (actual time=0.024..0.033 rows=2 loops=1)
> Filter: (protobundle_id = 1)
> -> Hash (cost=20.00..20.00 rows=1000 width=8) (actual
> time=581.802..581.802 rows=0 loops=1)
> -> Seq Scan on elements (cost=0.00..20.00 rows=1000 width=8) (actual
> time=0.172..405.243 rows=185535 loops=1)

The planner thinks that the sequential scan on elements will return 1000
rows, but it actually returned 185000. Did you ANALYZE this table recently?

Afterthought: It would be nice if the database was smart enough to
analyze a table of its own accord when a sequential scan returns more
than, say, 20 times what it was supposed to.

Paul

> Total runtime: 593.843 ms
> (12 rows)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>