On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett <nik9000@xxxxxxxxx> wrote: > > > On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner > <Kevin.Grittner@xxxxxxxxxxxx> wrote: >> >> Tom Lane <tgl@xxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
