Re: SELECT DISTINCT Performance Issue - Mailing list pgsql-performance

From K C Lau
Subject Re: SELECT DISTINCT Performance Issue
Date
Msg-id 6.2.1.2.0.20050606224010.05df3578@localhost
Whole thread Raw
In response to Re: SELECT DISTINCT Performance Issue  (PFC <lists@boutiquenumerique.com>)
List pgsql-performance
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 Pla
yerID='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..1402
3.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.



pgsql-performance by date:

Previous
From: PFC
Date:
Subject: Re: Performance nightmare with dspam (urgent) (resolved)
Next
From: Michael Stone
Date:
Subject: Re: Performance nightmare with dspam (urgent) (resolved)