| From: | "Doiron, Daniel" <DoironD(at)advisory(dot)com> |
|---|---|
| To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Nested Loop vs Hash Join based on predicate? |
| Date: | 2016-03-16 20:23:05 |
| Message-ID: | D30F38E2.24DF8%doirond@advisory.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
I have the following queries:
EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING)
select […]
from f_calc_service a11,
d_patient_type a12
where a11.d_patient_pop_id in (336)
and a11.d_patient_type_id = a12.id
and a12.short_name = 'I'
group by a11.d_rate_schedule_id,
a11.d_payer_id,
a11.d_patient_pop_id,
a11.d_patient_type_id
;
And
EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING)
select […]
from f_calc_service a11,
d_patient_type a12
where a11.d_patient_pop_id in (336)
and a11.d_patient_type_id = a12.id
and a12.short_name = 'O'
group by a11.d_rate_schedule_id,
a11.d_payer_id,
a11.d_patient_pop_id,
a11.d_patient_type_id
;
Making this one change from short_name = ‘I’ to short_name = ‘O’ changes the query execution from 200k ms to 280ms. The first one chooses a Nested Loop, the second chooses a hash join. How do I get them both to choose the same? There are no values for d_patient_pop_id in (336) and short_name = ‘I’.
Thanks!
Dan
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Pavel Stehule | 2016-03-16 21:01:24 | Re: Nested Loop vs Hash Join based on predicate? |
| Previous Message | Andreas Joseph Krogh | 2016-03-16 15:17:40 | Re: Searching GIN-index (FTS) and sort by timestamp-column |