Re: Some question

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