pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT DISTINCT Performance Issue
6+ messages / 4 participants
[nested] [flat]
* SELECT DISTINCT Performance Issue
@ 2005-06-03 01:56 K C Lau <KCLau@attglobal.net>
2005-06-06 11:45 ` Re: SELECT DISTINCT Performance Issue PFC <lists@boutiquenumerique.com>
2005-06-08 13:34 ` Re: SELECT DISTINCT Performance Issue George Essig <george.essig@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: K C Lau @ 2005-06-03 01:56 UTC (permalink / raw)
To: pgsql-performance
Hi All,
We are testing PostgreSQL 8.0.3 on MS Windows for porting an OLTP system
from MS SqlServer.
We got a major performance issue which seems to boil down to the following
type of query:
select DISTINCT ON (PlayerID) PlayerID,AtDate from Player where
PlayerID='22220' order by PlayerID desc, AtDate desc;
The Player table has primary key (PlayerID, AtDate) representing data over
time and the query gets the latest data for a player.
With enable_seqscan forced off (which I'm not sure if that should be done
for a production system), the average query still takes a very long time to
return a record:
esdt=> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate from
Player
where PlayerID='22220' order by PlayerID desc, AtDate desc;
Unique (cost=0.00..2507.66 rows=1 width=23) (actual time=0.000..187.000
rows=1 loops=1)
-> Index Scan Backward using pk_player on player (cost=0.00..2505.55
rows=8
43 width=23) (actual time=0.000..187.000 rows=1227 loops=1)
Index Cond: ((playerid)::text = '22220'::text)
Total runtime: 187.000 ms
It appears that all the 1227 data records for that player were searched,
even when doing a backward index scan. I would presume that, after locating
the index for the highest AtDate, only the first data record needs to be
retrieved.
The following summary of tests seems to confirm my observation. They were
done on a quiet system (MS Windows 2000 Server, P4 3.0GHz with
Hyperthreading, 1GB Memory, PostgreSQL shared_buffers = 50000), starting
with a test database before doing a vacuum:
set enable_seqscan = off;
select Total runtime: 187.000 ms
again: Total runtime: 78.000 ms
vacuum analyze verbose player;
select Total runtime: 47.000 ms
again: Total runtime: 47.000 ms
reindex table player;
select Total runtime: 78.000 ms
again: Total runtime: 63.000 ms
cluster pk_player on player;
select Total runtime: 16.000 ms
again: Total runtime: 0.000 ms
set enable_seqscan = on;
analyze verbose player;
select Total runtime: 62.000 ms
again: Total runtime: 78.000 ms
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
Any clue or suggestions would be most appreciated. If you need further info
or the full explain logs, please let me know.
Regards,
KC.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SELECT DISTINCT Performance Issue
2005-06-03 01:56 SELECT DISTINCT Performance Issue K C Lau <KCLau@attglobal.net>
@ 2005-06-06 11:45 ` PFC <lists@boutiquenumerique.com>
2005-06-06 14:54 ` Re: SELECT DISTINCT Performance Issue K C Lau <kclau60@netvigator.com>
1 sibling, 1 reply; 6+ messages in thread
From: PFC @ 2005-06-06 11:45 UTC (permalink / raw)
To: K C Lau <KCLau@attglobal.net>; pgsql-performance
> 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 ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SELECT DISTINCT Performance Issue
2005-06-03 01:56 SELECT DISTINCT Performance Issue K C Lau <KCLau@attglobal.net>
2005-06-06 11:45 ` Re: SELECT DISTINCT Performance Issue PFC <lists@boutiquenumerique.com>
@ 2005-06-06 14:54 ` K C Lau <kclau60@netvigator.com>
0 siblings, 0 replies; 6+ messages in thread
From: K C Lau @ 2005-06-06 14:54 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SELECT DISTINCT Performance Issue
2005-06-03 01:56 SELECT DISTINCT Performance Issue K C Lau <KCLau@attglobal.net>
@ 2005-06-08 13:34 ` George Essig <george.essig@gmail.com>
2005-06-08 14:25 ` Re: SELECT DISTINCT Performance Issue K C Lau <kclau60@netvigator.com>
1 sibling, 1 reply; 6+ messages in thread
From: George Essig @ 2005-06-08 13:34 UTC (permalink / raw)
To: K C Lau <KCLau@attglobal.net>; +Cc: pgsql-performance
On 6/2/05, K C Lau <KCLau@attglobal.net> wrote:
...
>
> select DISTINCT ON (PlayerID) PlayerID,AtDate from Player where
> PlayerID='22220' order by PlayerID desc, AtDate desc;
> The Player table has primary key (PlayerID, AtDate) representing data over
> time and the query gets the latest data for a player.
>
>
...
> esdt=> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate from
> Player
> where PlayerID='22220' order by PlayerID desc, AtDate desc;
> Unique (cost=0.00..2507.66 rows=1 width=23) (actual time=0.000..187.000
> rows=1 loops=1)
> -> Index Scan Backward using pk_player on player (cost=0.00..2505.55
> rows=8
> 43 width=23) (actual time=0.000..187.000 rows=1227 loops=1)
> Index Cond: ((playerid)::text = '22220'::text)
> Total runtime: 187.000 ms
>
Is PlayerID an integer datatype or a text datatype. It seems like
PlayerID should be an integer data type, but postgres treats PlayerID
as a text data type. This is because the value '22220' is quoted in
your query. Also, the explain analyze output shows "Index Cond:
((playerid)::text = '22220'::text".
George Essig
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SELECT DISTINCT Performance Issue
2005-06-03 01:56 SELECT DISTINCT Performance Issue K C Lau <KCLau@attglobal.net>
2005-06-08 13:34 ` Re: SELECT DISTINCT Performance Issue George Essig <george.essig@gmail.com>
@ 2005-06-08 14:25 ` K C Lau <kclau60@netvigator.com>
2005-06-08 14:51 ` Re: SELECT DISTINCT Performance Issue George Essig <george.essig@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: K C Lau @ 2005-06-08 14:25 UTC (permalink / raw)
To: George Essig <george.essig@gmail.com>; +Cc: pgsql-performance
Both keys are text fields. Does it make any difference if PlayerID were
integer?
BTW, I think the real performance problem is when we use SELECT ... ORDER
BY PlayerID DESC, AtDate DESC LIMIT 1 in a VIEW. Please see my subsequent
email http://archives.postgresql.org/pgsql-performance/2005-06/msg00110.php
on this show-stopper problem for which I still have no clue how to get
around. Suggestions are much appreciated.
Thanks and regards, KC.
At 21:34 05/06/08, George Essig wrote:
>On 6/2/05, K C Lau <KCLau@attglobal.net> wrote:
>...
> >
> > select DISTINCT ON (PlayerID) PlayerID,AtDate from Player where
> > PlayerID='22220' order by PlayerID desc, AtDate desc;
> > The Player table has primary key (PlayerID, AtDate) representing data over
> > time and the query gets the latest data for a player.
> >
> >
>...
> > esdt=> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate from
> > Player
> > where PlayerID='22220' order by PlayerID desc, AtDate desc;
> > Unique (cost=0.00..2507.66 rows=1 width=23) (actual time=0.000..187.000
> > rows=1 loops=1)
> > -> Index Scan Backward using pk_player on player (cost=0.00..2505.55
> > rows=8
> > 43 width=23) (actual time=0.000..187.000 rows=1227 loops=1)
> > Index Cond: ((playerid)::text = '22220'::text)
> > Total runtime: 187.000 ms
> >
>
>Is PlayerID an integer datatype or a text datatype. It seems like
>PlayerID should be an integer data type, but postgres treats PlayerID
>as a text data type. This is because the value '22220' is quoted in
>your query. Also, the explain analyze output shows "Index Cond:
>((playerid)::text = '22220'::text".
>
>George Essig
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SELECT DISTINCT Performance Issue
2005-06-03 01:56 SELECT DISTINCT Performance Issue K C Lau <KCLau@attglobal.net>
2005-06-08 13:34 ` Re: SELECT DISTINCT Performance Issue George Essig <george.essig@gmail.com>
2005-06-08 14:25 ` Re: SELECT DISTINCT Performance Issue K C Lau <kclau60@netvigator.com>
@ 2005-06-08 14:51 ` George Essig <george.essig@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: George Essig @ 2005-06-08 14:51 UTC (permalink / raw)
To: K C Lau <kclau60@netvigator.com>; +Cc: pgsql-performance
On 6/8/05, K C Lau <kclau60@netvigator.com> wrote:
> Both keys are text fields. Does it make any difference if PlayerID were
> integer?
>
It can make a difference in speed and integrity. If the column is an
integer, the storage on disk could be smaller for the column and the
related indexes. If the the column is an integer, it would not be
possible to have a value like 'arbitrary value that looks nothing like
an integer'.
George Essig
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-08 14:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-03 01:56 SELECT DISTINCT Performance Issue K C Lau <KCLau@attglobal.net>
2005-06-06 11:45 ` PFC <lists@boutiquenumerique.com>
2005-06-06 14:54 ` K C Lau <kclau60@netvigator.com>
2005-06-08 13:34 ` George Essig <george.essig@gmail.com>
2005-06-08 14:25 ` K C Lau <kclau60@netvigator.com>
2005-06-08 14:51 ` George Essig <george.essig@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox