pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to "unique-ify" HUGE table?
7+ messages / 5 participants
[nested] [flat]
* How to "unique-ify" HUGE table?
@ 2008-12-23 17:25 Kynn Jones <kynnjo@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Kynn Jones @ 2008-12-23 17:25 UTC (permalink / raw)
To: pgsql-performance
Hi everyone!
I have a very large 2-column table (about 500M records) from which I want to
remove duplicate records.
I have tried many approaches, but they all take forever.
The table's definition consists of two short TEXT columns. It is a
temporary table generated from a query:
CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... ;
Initially I tried
CREATE TEMP TABLE huge_table AS SELECT DISTINCT x, y FROM ... ;
but after waiting for nearly an hour I aborted the query, and repeated it
after getting rid of the DISTINCT clause.
Everything takes forever with this monster! It's uncanny. Even printing it
out to a file takes forever, let alone creating an index for it.
Any words of wisdom on how to speed this up would be appreciated.
TIA!
Kynn
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to "unique-ify" HUGE table?
@ 2008-12-23 17:34 Scott Marlowe <scott.marlowe@gmail.com>
parent: Kynn Jones <kynnjo@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2008-12-23 17:34 UTC (permalink / raw)
To: Kynn Jones <kynnjo@gmail.com>; +Cc: pgsql-performance
On Tue, Dec 23, 2008 at 10:25 AM, Kynn Jones <kynnjo@gmail.com> wrote:
> Hi everyone!
> I have a very large 2-column table (about 500M records) from which I want to
> remove duplicate records.
> I have tried many approaches, but they all take forever.
> The table's definition consists of two short TEXT columns. It is a
> temporary table generated from a query:
>
> CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... ;
> Initially I tried
> CREATE TEMP TABLE huge_table AS SELECT DISTINCT x, y FROM ... ;
> but after waiting for nearly an hour I aborted the query, and repeated it
> after getting rid of the DISTINCT clause.
> Everything takes forever with this monster! It's uncanny. Even printing it
> out to a file takes forever, let alone creating an index for it.
> Any words of wisdom on how to speed this up would be appreciated.
Did you try cranking up work_mem to something that's a large
percentage (25 to 50%) of total memory?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to "unique-ify" HUGE table?
@ 2008-12-23 17:39 D'Arcy J.M. Cain <darcy@druid.net>
parent: Kynn Jones <kynnjo@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: D'Arcy J.M. Cain @ 2008-12-23 17:39 UTC (permalink / raw)
To: Kynn Jones <kynnjo@gmail.com>; +Cc: pgsql-performance
On Tue, 23 Dec 2008 12:25:48 -0500
"Kynn Jones" <kynnjo@gmail.com> wrote:
> Hi everyone!
> I have a very large 2-column table (about 500M records) from which I want to
> remove duplicate records.
>
> I have tried many approaches, but they all take forever.
>
> The table's definition consists of two short TEXT columns. It is a
> temporary table generated from a query:
>
> CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... ;
>
> Initially I tried
>
> CREATE TEMP TABLE huge_table AS SELECT DISTINCT x, y FROM ... ;
>
> but after waiting for nearly an hour I aborted the query, and repeated it
Do you have an index on x and y? Also, does this work better?
CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... GROUP BY x, y;
What does ANALYZE EXPLAIN have to say?
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to "unique-ify" HUGE table?
@ 2008-12-23 18:14 George Pavlov <gpavlov@mynewplace.com>
parent: Kynn Jones <kynnjo@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: George Pavlov @ 2008-12-23 18:14 UTC (permalink / raw)
To: Kynn Jones <kynnjo@gmail.com>; pgsql-performance
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:
CREATE TEMP TABLE huge_table AS SELECT x, y FROM foo GROUP BY 1, 2;
Note that you may be tempted to add an index on foo(x,y), but I don't
think that helps (or at least I have not been able to hit the index in
similar situations).
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Kynn Jones
> Sent: Tuesday, December 23, 2008 9:26 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] How to "unique-ify" HUGE table?
>
> Hi everyone!
>
> I have a very large 2-column table (about 500M records) from which I
> want to remove duplicate records.
>
> I have tried many approaches, but they all take forever.
>
> The table's definition consists of two short TEXT columns. It is a
> temporary table generated from a query:
>
>
> CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... ;
>
> Initially I tried
>
> CREATE TEMP TABLE huge_table AS SELECT DISTINCT x, y FROM ... ;
>
> but after waiting for nearly an hour I aborted the query, and repeated
> it after getting rid of the DISTINCT clause.
>
> Everything takes forever with this monster! It's uncanny. Even
> printing it out to a file takes forever, let alone creating an index
> for it.
>
> Any words of wisdom on how to speed this up would be appreciated.
>
> TIA!
>
> Kynn
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to "unique-ify" HUGE table?
@ 2008-12-23 18:37 Scott Marlowe <scott.marlowe@gmail.com>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2008-12-23 18:37 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Kynn Jones <kynnjo@gmail.com>; pgsql-performance
On Tue, Dec 23, 2008 at 11:14 AM, George Pavlov <gpavlov@mynewplace.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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to "unique-ify" HUGE table?
@ 2008-12-23 19:47 Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stefan Kaltenbrunner @ 2008-12-23 19:47 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: George Pavlov <gpavlov@mynewplace.com>; Kynn Jones <kynnjo@gmail.com>; pgsql-performance
Scott Marlowe wrote:
> On Tue, Dec 23, 2008 at 11:14 AM, George Pavlov <gpavlov@mynewplace.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.
Not surprising - this is a known limitation in all released versions of
postgresql (GROUP BY can use hashing and sorting - DISTINCT only
sorting). 8.4 is going to improve that though.
Stefan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to "unique-ify" HUGE table?
@ 2008-12-24 18:56 Kynn Jones <kynnjo@gmail.com>
parent: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
0 siblings, 0 replies; 7+ messages in thread
From: Kynn Jones @ 2008-12-24 18:56 UTC (permalink / raw)
To: pgsql-performance
Thank you all for the very helpful advice. Upping work_mem made it possible
for me to generate the table within this century without bringing the server
to a near standstill. I have not yet experimented with GROUP BY, but I'll
do this next.
Cheers,
Kynn
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-12-24 18:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-23 17:25 How to "unique-ify" HUGE table? Kynn Jones <kynnjo@gmail.com>
2008-12-23 17:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-23 17:39 ` D'Arcy J.M. Cain <darcy@druid.net>
2008-12-23 18:14 ` George Pavlov <gpavlov@mynewplace.com>
2008-12-23 18:37 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-23 19:47 ` Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2008-12-24 18:56 ` Kynn Jones <kynnjo@gmail.com>
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