Re: [BUGS] BUG #8130: Hashjoin still gives issues

> -----Original Message-----
>

>
> The original query:
>
> select * from ambit_privateevent_calendars as a, ambit_privateevent as
> b, ambit_calendarsubscription as c, ambit_calendar as d where
> c.calendar_id = d.id and a.privateevent_id = b.id and c.user_id = 1270
> and c.calendar_id = a.calendar_id and c.STATUS IN (1, 8, 2, 15, 18, 4,
> 12, 20) and not b.main_recurrence = true;
>
> select b.id from ambit_privateevent_calendars as a, ambit_privateevent
> as b, ambit_calendarsubscription as c, ambit_calendar as d where
> c.calendar_id = d.id and a.privateevent_id = b.id and c.user_id = 1270
> and c.calendar_id = a.calendar_id and c.STATUS IN (1, 8, 2, 15, 18, 4,
> 12, 20) and not b.main_recurrence = true;
>
> (select * => select b.id, the star query is *fastest*)
>
> We compare:
> http://explain.depesz.com/s/jRx
> http://explain.depesz.com/s/eKE
>
>
> By setting "set enable_hashjoin = off;" performance in our entire
> application increased 30 fold in throughput, which was a bit unexpected
> but highly appreciated. The result of the last query switch the
> mergejoin:
>
> http://explain.depesz.com/s/AWB
>
> It is also visible that after hashjoin is off, the b.id query is faster
> than the * query (what would be expected).
>
>
> Our test machine is overbudgetted, 4x the memory of the entire database
> ~4GB, and uses the PostgreSQL stock settings.
>
>
> Stefan
>

I'd suggest that you adjust Postgres configuration, specifically memory settings (buffer_cache, work_mem, effective_cache_size), to reflect your hardware config, and see how it affects your query.

Regards,
Igor Neyman