Re: Bad plan using join on VALUES

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.