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.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org

Reply via email to