Re: getting most recent row efficiently
От | Antonio Fiol Bonnín |
---|---|
Тема | Re: getting most recent row efficiently |
Дата | |
Msg-id | 3C1F075F.7030201@w3ping.com обсуждение исходный текст |
Ответ на | Re: getting most recent row efficiently ("SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com>) |
Список | pgsql-general |
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 > >. >
В списке pgsql-general по дате отправления: