X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D181E5298D
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Jun 2005 10:34:26 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 28146-03
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Jun 2005 13:34:25 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.197])
	by svr1.postgresql.org (Postfix) with ESMTP id 45DB152987
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Jun 2005 10:34:24 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 69so250375wra
	for <pgsql-performance@postgresql.org>;
	Wed, 08 Jun 2005 06:34:29 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=CHXg3AITMPuQEH0UWe2mMZp7Es1iDBJxqnuzmHqlNcn9KJreJFJ/5wdSIwt4iDdU1+RfkpB5T+H9StpYiuGCX6DNdfvSuUCCKcGp7AmajaMe+HoVWJiAxd1ew+9E7RtrHApR39++aMbeGzVXkIEcZ4tKir0xlSL1c0IpnlcNbNQ=
Received: by 10.54.13.66 with SMTP id 66mr4534536wrm;
	Wed, 08 Jun 2005 06:34:29 -0700 (PDT)
Received: by 10.54.96.18 with HTTP; Wed, 8 Jun 2005 06:34:29 -0700 (PDT)
Message-ID: <6744b38505060806342e6a07d@mail.gmail.com>
Date: Wed, 8 Jun 2005 08:34:29 -0500
From: George Essig <george.essig@gmail.com>
Reply-To: George Essig <george.essig@gmail.com>
To: K C Lau <KCLau@attglobal.net>
Subject: Re: SELECT DISTINCT Performance Issue
Cc: pgsql-performance@postgresql.org
In-Reply-To: <6.2.1.2.0.20050602190756.05ab2c70@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <6.2.1.2.0.20050602190756.05ab2c70@localhost>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.097 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200506/167
X-Sequence-Number: 12804

On 6/2/05, K C Lau <KCLau@attglobal.net> wrote:
...
>=20
> select DISTINCT ON (PlayerID) PlayerID,AtDate from Player  where
> PlayerID=3D'22220' order by PlayerID desc, AtDate desc;
> The Player table has primary key (PlayerID, AtDate) representing data ove=
r
> time and the query gets the latest data for a player.
>=20
>
...=20
> esdt=3D> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate fr=
om
> Player
>   where PlayerID=3D'22220' order by PlayerID desc, AtDate desc;
>   Unique  (cost=3D0.00..2507.66 rows=3D1 width=3D23) (actual time=3D0.000=
..187.000
> rows=3D1 loops=3D1)
>     ->  Index Scan Backward using pk_player on player  (cost=3D0.00..2505=
.55
> rows=3D8
> 43 width=3D23) (actual time=3D0.000..187.000 rows=3D1227 loops=3D1)
>           Index Cond: ((playerid)::text =3D '22220'::text)
>   Total runtime: 187.000 ms
>=20

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 =3D '22220'::text".

George Essig