| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
| Cc: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Very ineffective plan with merge join |
| Date: | 2010-04-15 21:24:46 |
| Message-ID: | 24590.1271366686@sss.pgh.pa.us |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
> below is an example of interesting query and two plans - the bad plan, which
> uses merge join and big sorting, took 216 sec, and good plan with merge join disabled took
> 8 sec.
The "good" plan seems to be fast mainly because of heavily cached inner
indexscans. If that's the normal operating state for this database, you
should try reducing random_page_cost.
Also, as Pavel noted, the sub-join size estimates aren't very good, and
those overestimates are discouraging it from using inner-indexscan
nestloops. I'm not sure how much it would help to increase the
statistics targets, but that would be worth trying.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bruce Momjian | 2010-04-15 21:31:13 | Re: Rogue TODO list created |
| Previous Message | Oleg Bartunov | 2010-04-15 21:12:15 | Re: Very ineffective plan with merge join |