From: K C Lau <kclau60@netvigator.com>
To: pgsql-performance@postgresql.org
Subject: SELECT LIMIT 1 VIEW Performance Issue
Date: Wed, 15 Jun 2005 10:46:56 +0800
Message-ID: <6.2.1.2.0.20050615100524.04f9d6a8@localhost> (raw)
Hi All,
I previously posted the following as a sequel to my SELECT DISTINCT
Performance Issue question. We would most appreciate any clue or
suggestions on how to overcome this show-stopping issue. We are using 8.0.3
on Windows.
Is it a known limitation when using a view with SELECT ... LIMIT 1?
Would the forthcoming performance enhancement with MAX help when used
within a view, as in:
create or replace view VCurPlayer as select * from Player a
where a.AtDate = (select Max(b.AtDate) from Player b where a.PlayerID =
b.PlayerID);
select PlayerID,AtDate from VCurPlayer where PlayerID='22220';
Thanks and regards,
KC.
---------
At 19:45 05/06/06, PFC wrote:
>>Previously, we have also tried to use LIMIT 1 instead of DISTINCT, but
>>the performance was no better:
>>select PlayerID,AtDate from Player where PlayerID='22220' order by
>>PlayerID desc, AtDate desc LIMIT 1
>
> The DISTINCT query will pull out all the rows and keep only one,
> so the
>one with LIMIT should be faster. Can you post explain analyze of the LIMIT
>query ?
Actually the problem with LIMIT 1 query is when we use views with the LIMIT
1 construct. The direct SQL is ok:
esdt=> explain analyze select PlayerID,AtDate from Player where
PlayerID='22220'
order by PlayerID desc, AtDate desc LIMIT 1;
Limit (cost=0.00..1.37 rows=1 width=23) (actual time=0.000..0.000 rows=1
loops=1)
-> Index Scan Backward using pk_player on player (cost=0.00..16074.23
rows=11770 width=23) (actual time=0.000..0.000 rows=1 loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Total runtime: 0.000 ms
esdt=> create or replace view VCurPlayer3 as select * from Player a
where AtDate = (select b.AtDate from Player b where a.PlayerID = b.PlayerID
order by b.PlayerID desc, b.AtDate desc LIMIT 1);
esdt=> explain analyze select PlayerID,AtDate,version from VCurPlayer3
where PlayerID='22220';
Index Scan using pk_player on player a (cost=0.00..33072.78 rows=59
width=27)
(actual time=235.000..235.000 rows=1 loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Filter: ((atdate)::text = ((subplan))::text)
SubPlan
-> Limit (cost=0.00..1.44 rows=1 width=23) (actual
time=0.117..0.117 rows=1 loops=1743)
-> Index Scan Backward using pk_player on player
b (cost=0.00..14023.67 rows=9727 width=23) (actual time=0.108..0.108
rows=1 loops=1743)
Index Cond: (($0)::text = (playerid)::text)
Total runtime: 235.000 ms
The problem appears to be in the loops=1743 scanning all 1743 data records
for that player.
Regards, KC.
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: kclau60@netvigator.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <6.2.1.2.0.20050615100524.04f9d6a8@localhost>
* 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