Re: Best way to get the latest revision from a table

Distinct on is working really well! If I need to be able to index something
I might start thinking along those lines.

On Fri, Jan 21, 2011 at 12:13 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett <nik9000(at)gmail(dot)com>
> wrote:
> >
> >
> > On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner
> > <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> >>
> >> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>
> >> > Shaun's example is a bit off
> >>
> >> > As for speed, either one might be faster in a particular
> >> > situation.
> >>
> >> After fixing a mistake in my testing and learning from Tom's example
> >> I generated queries against the OP's test data which produce
> >> identical results, and I'm finding no significant difference between
> >> run times for the two versions. The OP should definitely try both
> >> against the real tables.
> >>
> > <snip>
> >>
> >> -Kevin
> >
> > After trying both against the real tables DISTINCT ON seems to be about
> two
> > orders of magnitude faster than the other options.
>
> What I've often done in these situations is add a Boolean to the table
> that defaults to true, and an ON INSERT trigger that flips the Boolean
> for any existing row with the same key to false. Then you can just do
> something like "SELECT * FROM tab WHERE latest". And you can create
> partial indexes etc: CREATE INDEX foo ON tab (a) WHERE latest.
>
> Although if using DISTINCT ON is working, no reason to do anything
> more complicated.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>