> -----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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
