DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=DIzc8t4pbBqas/KzSbCJ7oApyJUKtGqIt64EExhDbscgfTNDVGz8vcktWk/DnDfPrY
	HQBxMZrnPseT/vpc/jjglratk7YkaWqFecdFcFgJn1heWPPuRh4ZhNVXikUNqz8CNIB3
	k7O5eFQuVaEzO6jpFqdrSwfc1zc5rVhb2CiYc=
Message-ID: <4BBC31DC.1080605@gmail.com>
Date: Wed, 07 Apr 2010 09:18:52 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: luvar@plaintext.sk, pgsql-performance@postgresql.org
Subject: Re: Some question
References: <201003311746.38192.luvar@plaintext.sk>
	<p2kdcc563d11004061145g928107c0gc73c2b860eee677@mail.gmail.com>
In-Reply-To: <p2kdcc563d11004061145g928107c0gc73c2b860eee677@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Scott Marlowe wrote:
> 2010/3/31 Ľubomír Varga <luvar@plaintext.sk>:
>   
>> Hi, stright to my "problem":
>> If I try to select constant 1 from table with two rows, it will be something
>> like this:
>>
>> explain
>> SELECT * FROM t_route
>>        WHERE t_route.route_type_fk = (SELECT id FROM t_route_type WHERE type = 2)
>>        limit 4;
>>
>> "Limit  (cost=1.02..1.91 rows=4 width=2640)"
>> "  InitPlan"
>> "    ->  Seq Scan on t_route_type  (cost=0.00..1.02 rows=1 width=8)"
>> "          Filter: ("type" = 2)"
>> "  ->  Seq Scan on t_route  (cost=0.00..118115.25 rows=535090 width=2640)"
>> "        Filter: (route_type_fk = $0)"
>>
>>     
>
> Looking at this it looks like you're using prepared queries, which
> can't make as good of a decision as regular queries because the values
> are opaque to the planner.
>
> Can you provide us with the output of explain analyze of that query?
>   
ISTM that the initplan 'outputs' id as $0, so it is not a prepared 
query. Maybe EXPLAIN VERBOSE ANALYZE of the query reveals that better. 
But both plans show seqscans of the large table, so it is surprising 
that the performance is different, if the filter expression uses the 
same values. Are you sure the output SELECT id FROM t_route_type WHERE 
type = 2 is equal to 1?

regards,
Yeb Havinga