From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: kclau60@netvigator.com
To: pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: Thu, 22 Sep 2005 13:19:56 -0500
Message-ID: <s332af83.045@gwmta.wicourts.gov> (raw)
Have you tried the "best choice" pattern -- where you select the set of
candidate rows and then exclude those for which a better choice
exists within the set? I often get better results with this pattern than
with the alternatives. Transmuting your query to use this patter gives:
select PlayerID,AtDate from Player a where PlayerID='22220'
and not exists
(select * from Player b
where b.PlayerID = a.PlayerID and b.AtDate > a.AtDate);
>>> K C Lau <kclau60@netvigator.com> 09/21/05 11:21 PM >>>
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, kclau60@netvigator.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <s332af83.045@gwmta.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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