Re: SELECT LIMIT 1 VIEW Performance Issue - Mailing list pgsql-performance

Hi All,

Investigating further on this problem I brought up in June, the following
query with pg 8.0.3 on Windows scans all 1743 data records for a player:

esdt=> explain analyze select PlayerID,AtDate from Player a
  where PlayerID='22220' and AtDate = (select b.AtDate from Player b
  where b.PlayerID = a.PlayerID order by b.PlayerID desc, b.AtDate desc
LIMIT 1);

  Index Scan using pk_player on player a  (cost=0.00..2789.07 rows=9
width=23) (a
ctual time=51.046..51.049 rows=1 loops=1)
    Index Cond: ((playerid)::text = '22220'::text)
    Filter: ((atdate)::text = ((subplan))::text)
    SubPlan
      ->  Limit  (cost=0.00..0.83 rows=1 width=23) (actual
time=0.016..0.017 rows
=1 loops=1743)
            ->  Index Scan Backward using pk_player on player
b  (cost=0.00..970.
53 rows=1166 width=23) (actual time=0.011..0.011 rows=1 loops=1743)
                  Index Cond: ((playerid)::text = ($0)::text)
  Total runtime: 51.133 ms

Using a static value in the subquery produces the desired result below, but
since we use views for our queries (see last part of this email), we cannot
push the static value into the subquery:

esdt=> explain analyze select PlayerID,AtDate from Player a
  where PlayerID='22220' and AtDate = (select b.AtDate from Player b
  where b.PlayerID = '22220' order by b.PlayerID desc, b.AtDate desc LIMIT 1);

  Index Scan using pk_player on player a  (cost=0.75..4.26 rows=1 width=23)
(actu
al time=0.054..0.058 rows=1 loops=1)
    Index Cond: (((playerid)::text = '22220'::text) AND ((atdate)::text =
($0)::t
ext))
    InitPlan
      ->  Limit  (cost=0.00..0.75 rows=1 width=23) (actual
time=0.028..0.029 rows
=1 loops=1)
            ->  Index Scan Backward using pk_player on player
b  (cost=0.00..1323
.05 rows=1756 width=23) (actual time=0.023..0.023 rows=1 loops=1)
                  Index Cond: ((playerid)::text = '22220'::text)
  Total runtime: 0.149 ms

The Player table has a primary key on PlayerID, AtDate. Is there a way to
stop the inner-most index scan looping all 1743 data records for that
player?  Is that a bug or known issue?

BTW, I tried using 8.1 beta2 on Windows and its performance is similar, I
have also tried other variants such as MAX and DISTINCT but with no success.

Any help is most appreciated.

Best regards,
KC.


At 10:46 05/06/15, K C Lau wrote:
>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.
>
>---------
>
>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