Skip site navigation (1) Skip section navigation (2)

Re: SELECT LIMIT 1 VIEW Performance Issue

From: K C Lau <kclau60(at)netvigator(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: 2005-09-22 04:21:18
Message-ID: 6.2.1.2.0.20050922102035.07bcf2e0@localhost (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-performance by date

Next:From: Simon RiggsDate: 2005-09-22 08:24:10
Subject: Re: Nested Loop trouble : Execution time increases more
Previous:From: Tom LaneDate: 2005-09-22 03:12:57
Subject: Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group