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 <robertmh...@gmail.com> wrote: > On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett <nik9...@gmail.com> > wrote: > > > > > > On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner > > <kevin.gritt...@wicourts.gov> wrote: > >> > >> Tom Lane <t...@sss.pgh.pa.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 >
