Re: getting most recent row efficiently

There are some cases where using views is not always the most practical
solution.

If you will only query for a given doctor's current favorites, and you
do not care about other doctors' (listing all) or other (previous)
favorites, for that matter, you can use:

SELECT * FROM doctor_favorites WHERE doctor_id=XXX ORDER BY doctor_id,
local_ts DESC LIMIT 1;

The index you want is ON doctor_favorites (doctor_id, local_ts), I bet.

Simpler is not always better, though.

Antonio

>
>monitor-prod=# \d doctor_favorites
> Table "doctor_favorites"
> Attribute | Type | Modifier
>-----------------+--------------------------+------------------------
> favorites | integer |
> remote_ts | timestamp with time zone | not null
> local_ts | timestamp with time zone | not null default now()
> med_practice_id | integer |
> doctor_id | integer |
>Indices: docid_index,
> docid_medpracid_index,
> localts_index,
> medpracid_index
>
>monitor-test=# \d current_doctor_favorites
> View "current_doctor_favorites"
> Attribute | Type | Modifier
>-----------------+---------+----------
> doctor_id | integer |
> med_practice_id | integer |
> favorites | integer |
>View definition: SELECT df.doctor_id, df.med_practice_id, df.favorites
>FROM doctor_favorites df WHERE (df.local_ts = (SELECT
>max(doctor_favorites.local_ts) AS max FROM doctor_favorites WHERE
>((doctor_favorites.doctor_id = df.doctor_id) AND
>(doctor_favorites.med_practice_id = df.med_practice_id))));
>
>
>monitor-test=# explain SELECT df.doctor_id, df.med_practice_id,
>df.favorites FROM doctor_favorites df WHERE (df.local_ts = (SELECT
>max(doctor_favorites.local_ts) AS max FROM doctor_favorites WHERE
>((doctor_favorites.doctor_id = df.doctor_id) AND
>(doctor_favorites.med_practice_id = df.med_practice_id))));
>NOTICE: QUERY PLAN:
>
>Seq Scan on doctor_favorites df (cost=0.00..9564.75 rows=47 width=12)
> SubPlan
> -> Aggregate (cost=2.02..2.02 rows=1 width=8)
> -> Index Scan using docid_medpracid_index on
>doctor_favorites (cost=0.00..2.02 rows=1 width=8)
>
>EXPLAIN
>monitor-test=#
>
>monitor-prod=# \d doctor_metrics
> View "doctor_metrics"
> Attribute | Type | Modifier
>-----------------+---------+----------
> doctor_id | integer |
> med_practice_id | integer |
> lab_requests | integer |
> lab_results | integer |
> rx_auth | integer |
> transcriptions | integer |
> omnidocs | integer |
> phone | integer |
> favorites | integer |
>View definition: SELECT CASE WHEN (doctor_tasks.doctor_id NOTNULL) THEN
>doctor_tasks.doctor_id ELSE current_doctor_favorites.doctor_id END AS
>doctor_id, CASE WHEN (doctor_tasks.med_practice_id NOTNULL) THEN
>doctor_tasks.med_practice_id ELSE
>current_doctor_favorites.med_practice_id END AS med_practice_id,
>doctor_tasks.lab_requests, doctor_tasks.lab_results,
>doctor_tasks.rx_auth, doctor_tasks.transcriptions,
>doctor_tasks.omnidocs, doctor_tasks.phone,
>current_doctor_favorites.favorites FROM (doctor_tasks FULL JOIN
>current_doctor_favorites USING (doctor_id, med_practice_id));
>
>monitor-prod=# explain SELECT CASE WHEN (doctor_tasks.doctor_id NOTNULL)
>THEN do
>ctor_tasks.doctor_id ELSE current_doctor_favorites.doctor_id END AS
>doctor_id, CASE WHEN (doctor_tasks.med_practice_id NOTNULL) THEN
>doctor_tasks.med_practice_id ELSE
>current_doctor_favorites.med_practice_id END AS med_practice_id,
>doctor_tasks.lab_requests, doctor_tasks.lab_results,
>doctor_tasks.rx_auth, doctor_tasks.transcriptions,
>doctor_tasks.omnidocs, doctor_tasks.phone,
>current_doctor_favorites.favorites FROM (doctor_tasks FULL JOIN
>current_doctor_favorites USING (doctor_id, med_practice_id));
>NOTICE: QUERY PLAN:
>
>Merge Join (cost=9566.05..9640.75 rows=1000 width=44)
> -> Index Scan using doctor_tasks_pkey on doctor_tasks
>(cost=0.00..59.00 rows=1000 width=32)
> -> Sort (cost=9566.05..9566.05 rows=47 width=12)
> -> Subquery Scan current_doctor_favorites (cost=0.00..9564.75
>rows=47 width=12)
> -> Seq Scan on doctor_favorites df (cost=0.00..9564.75
>rows=47 width=12)
> SubPlan
> -> Aggregate (cost=2.02..2.02 rows=1 width=8)
> -> Index Scan using docid_medpracid_index
>on doctor_favorites (cost=0.00..2.02 rows=1 width=8)
>
>EXPLAIN
>monitor-prod=#
>
>Thanks for your feedback,
>Fran
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>.
>