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@postgresql.org
>
>.
>