Skip site navigation (1) Skip section navigation (2)

Re: How to "unique-ify" HUGE table?

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
Cc: "Kynn Jones" <kynnjo(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: How to "unique-ify" HUGE table?
Date: 2008-12-23 18:37:07
Message-ID: dcc563d10812231037p1c5b08cfpe34a363ccaa6b37b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Dec 23, 2008 at 11:14 AM, George Pavlov <gpavlov(at)mynewplace(dot)com> wrote:
> You don't say what PG version you are on, but just for kicks you may try
> using GROUP BY instead of DISTINCT. Yes, the two should perform the
> same, but with 8.1 (or maybe 8.0) I had seen situations where GROUP BY
> was faster (admittedly this happened with more complex queries). So, try
> this:

Even in 8.3 it looks like group by is faster.  Tested it on a decent
sized table and group by used a hash agg and ran in ~600 ms, while
distinct used a sort and ran in 1300 ms.  That was on 500k rows.  On a
much larger table, one with about 10M rows, a similar statement runs
in 1500 ms with group by and in 2390 ms when run with distinct.

In response to

Responses

pgsql-performance by date

Next:From: Stefan KaltenbrunnerDate: 2008-12-23 19:47:36
Subject: Re: How to "unique-ify" HUGE table?
Previous:From: George PavlovDate: 2008-12-23 18:14:24
Subject: Re: How to "unique-ify" HUGE table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group