From: | K C Lau <kclau60(at)netvigator(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: SELECT LIMIT 1 VIEW Performance Issue |
Date: | 2005-09-23 12:17:03 |
Message-ID: | 6.2.1.2.0.20050923195457.02c4fd30@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
At 19:15 05/09/23, Simon Riggs wrote:
>select distinct on (PlayerID) PlayerID,AtDate from Player a
>where PlayerID='22220' order by PlayerId, AtDate Desc;
>
>Does that work for you?
>
>Best Regards, Simon Riggs
esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from
Player a where PlayerID='22220' order by PlayerId, AtDate Desc;
Unique (cost=1417.69..1426.47 rows=2 width=23) (actual
time=31.231..36.609 rows=1 loops=1)
-> Sort (cost=1417.69..1422.08 rows=1756 width=23) (actual
time=31.129..32.473 rows=1743 loops=1)
Sort Key: playerid, atdate
-> Index Scan using pk_player on player a (cost=0.00..1323.05
rows=1756 width=23) (actual time=0.035..6.575 rows=1743 loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Total runtime: 36.943 ms
The sort was eliminated with: order by PlayerId Desc, AtDate Desc:
esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from
Player a where PlayerID='22220' order by PlayerId Desc, AtDate Desc;
Unique (cost=0.00..1327.44 rows=2 width=23) (actual time=0.027..8.438
rows=1 loops=1)
-> Index Scan Backward using pk_player on player
a (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.022..4.950
rows=1743 loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Total runtime: 8.499 ms
That is the fastest of all queries looping the 1743 rows.
I do get the desired result by adding LIMIT 1:
esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from
Player a where PlayerID='22220' order by PlayerId Desc, AtDate Desc LIMIT 1;
Limit (cost=0.00..663.72 rows=1 width=23) (actual time=0.032..0.033
rows=1 loops=1)
-> Unique (cost=0.00..1327.44 rows=2 width=23) (actual
time=0.028..0.028 rows=1 loops=1)
-> Index Scan Backward using pk_player on player
a (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.022..0.022 rows=1
loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Total runtime: 0.094 ms
However, when I use that within a function in a view, it is slow again:
esdt=> create or replace function player_max_atdate (varchar(32)) returns
varchar(32) as $$
esdt$> select distinct on (PlayerID) AtDate from player where PlayerID= $1
order by PlayerID desc, AtDate desc limit 1;
esdt$> $$ language sql immutable;
CREATE FUNCTION
esdt=> create or replace view VCurPlayer3 as select * from Player where
AtDate = player_max_atdate(PlayerID);
CREATE VIEW
esdt=> explain analyze select PlayerID,AtDate from VCurPlayer3 where
PlayerID='22220';
Index Scan using pk_player on player (cost=0.00..1331.83 rows=9
width=23) (actual time=76.660..76.664 rows=1 loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Filter: ((atdate)::text = (player_max_atdate(playerid))::text)
Total runtime: 76.716 ms
Why wouldn't the function get the row as quickly as the direct sql does?
Best regards, KC.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-09-23 12:34:21 | Re: SELECT LIMIT 1 VIEW Performance Issue |
Previous Message | Dave Cramer | 2005-09-23 11:30:59 | Re: How to determine cause of performance problem? |