Skip site navigation (1) Skip section navigation (2)

Re: performance of IN (subquery)

From: Paul Tillotson <pntil(at)shentel(dot)net>
To: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: performance of IN (subquery)
Date: 2004-08-27 00:15:01
Message-ID: 412E7D05.6090700@shentel.net (view raw or flat)
Thread:
Lists: pgsql-general
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
>
>


In response to

Responses

pgsql-general by date

Next:From: CSNDate: 2004-08-27 01:49:08
Subject: owner orphaned databases
Previous:From: Cornelia BoenigkDate: 2004-08-26 22:53:00
Subject: Re: Problem to connect to the Windows Port

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group