Re: Bad plan using join on VALUES

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Listmail <lists(at)peufeu(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Bad plan using join on VALUES
Date: 2007-04-10 10:24:03
Message-ID: 20070410102403.GA31734@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Apr 10, 2007 at 10:57:43AM +0200, Listmail wrote:
>
> Table definition and problem query is below. I'm surprised...

Well, the planner probably guessed that in your case it's faster to
scan the table than to use the index (indexes are not free). Did it
choose wrong? If you disable the seqscan, does it get faster (set
enable_seqscan=false). Make sure you run both a few times to make sure
you're getting good results.

If it turns out the planner is wrong, you need to do some tuning, in
particular random_page_cost and effective_cache_size.
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=1.50..542.68 rows=216 width=58) (actual
> time=0.395..45.402 rows=350 loops=1)
> Hash Cond: (target.child_id = "*VALUES*".column1)
> -> Seq Scan on relations target (cost=0.00..440.29 rows=26329
> width=58) (actual time=0.011..8.213 rows=26329 loops=1)
> -> Hash (cost=0.75..0.75 rows=60 width=4) (actual time=0.096..0.096
> rows=60 loops=1)
> -> Values Scan on "*VALUES*" (cost=0.00..0.75 rows=60 width=4)
> (actual time=0.001..0.049 rows=60 loops=1)
> Total runtime: 45.594 ms

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-04-10 10:33:29 Re: Debian upgrade and PGSQL pid file
Previous Message Magnus Hagander 2007-04-10 10:20:45 Re: [GENERAL] programmatic way to fetch latest release for a given major.minor version