From: Yeb Havinga <yebhavinga@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: luvar@plaintext.sk
Cc: pgsql-performance@postgresql.org
Subject: Re: Some question
Date: Wed, 07 Apr 2010 09:18:52 +0200
Message-ID: <4BBC31DC.1080605@gmail.com> (raw)
In-Reply-To: <p2kdcc563d11004061145g928107c0gc73c2b860eee677@mail.gmail.com>
References: <201003311746.38192.luvar@plaintext.sk>
<p2kdcc563d11004061145g928107c0gc73c2b860eee677@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: yebhavinga@gmail.com, scott.marlowe@gmail.com, luvar@plaintext.sk
Subject: Re: Some question
In-Reply-To: <4BBC31DC.1080605@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox