pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGrabbing Newest Records From Duplicates
4+ messages / 2 participants
[nested] [flat]
* Grabbing Newest Records From Duplicates
@ 2007-03-16 15:28 Travis Whitton <tinymountain@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Travis Whitton @ 2007-03-16 15:28 UTC (permalink / raw)
To: pgsql-sql
Given the following test table, I want to grab only the newest record and
disregard any older duplicates based on name. Is this the most efficient way
to do it? Will the indicies even make a difference? The table below
demonstrates a simple proof of concept. My final table will have millions of
records; however, the test I'm doing now does not seem to use the indicies
(probably because there are too few records?).
optin=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
name | text |
time | date |
id | integer |
Indexes:
"idx_name" btree (name)
"idx_time" btree ("time")
optin=# explain select * from test t1 where not exists (select 1 from test
t2 where t2.name = t1.name and t2.time > t1.time);
Thanks,
Travis
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Grabbing Newest Records From Duplicates
@ 2007-03-16 15:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Travis Whitton <tinymountain@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-03-16 15:35 UTC (permalink / raw)
To: Travis Whitton <tinymountain@gmail.com>; +Cc: pgsql-sql
"Travis Whitton" <tinymountain@gmail.com> writes:
> Given the following test table, I want to grab only the newest record and
> disregard any older duplicates based on name. Is this the most efficient way
> to do it?
No, it's gonna be pretty awful. The best way I know of involves
DISTINCT ON (see the "weather reports" example in the SELECT reference
page). Unfortunately that's a Postgres-only construct. If you want
something portable then you'll need something messy with subqueries...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Grabbing Newest Records From Duplicates
@ 2007-03-16 15:55 Travis Whitton <tinymountain@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Travis Whitton @ 2007-03-16 15:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
One last question, using the "weather report" example, is it going to
improve performance for the DISTINCT ON query to add an index to the
location and time columns?
Thanks a lot,
Travis
On 3/16/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Travis Whitton" <tinymountain@gmail.com> writes:
> > Given the following test table, I want to grab only the newest record
> and
> > disregard any older duplicates based on name. Is this the most efficient
> way
> > to do it?
>
> No, it's gonna be pretty awful. The best way I know of involves
> DISTINCT ON (see the "weather reports" example in the SELECT reference
> page). Unfortunately that's a Postgres-only construct. If you want
> something portable then you'll need something messy with subqueries...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Grabbing Newest Records From Duplicates
@ 2007-03-16 16:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Travis Whitton <tinymountain@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-03-16 16:17 UTC (permalink / raw)
To: Travis Whitton <tinymountain@gmail.com>; +Cc: pgsql-sql
"Travis Whitton" <tinymountain@gmail.com> writes:
> One last question, using the "weather report" example, is it going to
> improve performance for the DISTINCT ON query to add an index to the
> location and time columns?
A two-column index matching the ORDER BY that you use might help,
or then again might not --- for full-table scans it's often faster
to sort than to try to use an index.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-03-16 16:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-16 15:28 Grabbing Newest Records From Duplicates Travis Whitton <tinymountain@gmail.com>
2007-03-16 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-16 15:55 ` Travis Whitton <tinymountain@gmail.com>
2007-03-16 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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