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
