Re: [PERFORM] Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On 2012-10-30 14:08:56 -0500, Petr Praus wrote: select count(*) from contest c left outer join contestparticipant cp on c.id=cp.contestId left outer join teammember tm on tm.contestparticipantid=cp.id left outer join staffmember sm on cp.id=sm.contestparticipantid left outer join person p on p.id=cp.personid left outer join personinfo pi on pi.id=cp.personinfoid where pi.lastname like '%b%' or pi.firstname like '%a%'; Btw, not really related to the question, but the way you use left joins here doesn't really make sense and does lead to inferior plans. As you restrict on 'pi', the rightmost table in a chain of left joins, there is no point in all those left joins. I would guess the overall plan is better if use straight joins. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
I did run each query several times, the results I posted are for ~10th run of the query. The zone reclaim mode is 0. On 2 November 2012 00:39, Scott Marlowe scott.marl...@gmail.com wrote: Two possibilities: caching. make sure to run each query several times in a row. zone reclaim mode. If this has gotten turned on turn it back off. How to tell: sysctl -n vm.zone_reclaim_mode Output should be 0. If it's not, then add this to /etc/sysctl.conf: vm.zone_reclaim_mode=0 and run: sudo sysctl -p and see if that helps.
Re: [PERFORM] Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Two possibilities: caching. make sure to run each query several times in a row. zone reclaim mode. If this has gotten turned on turn it back off. How to tell: sysctl -n vm.zone_reclaim_mode Output should be 0. If it's not, then add this to /etc/sysctl.conf: vm.zone_reclaim_mode=0 and run: sudo sysctl -p and see if that helps. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
