From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Cranch <jdc41@cam.ac.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Rapidly finding maximal rows
Date: Fri, 14 Oct 2011 00:25:53 -0400
Message-ID: <13279.1318566353@sss.pgh.pa.us> (raw)
In-Reply-To: <Prayer.1.3.4.1110102259170.8390@hermes-2.csi.cam.ac.uk>
References: <Prayer.1.3.4.1110102259170.8390@hermes-2.csi.cam.ac.uk>
James Cranch <jdc41@cam.ac.uk> writes:
> I have a slow query. I expect that there is either a more intelligent way
> to write it, or that one could make some indexes that would speed it up.
> I've tried various indexes, and am not getting anywhere.
> I'd be grateful for any suggestions. Reasonably full details are below.
Two bits of advice:
1. Avoid unnecessary use of WITH. It acts as an optimization fence,
which you don't want here. In particular, the only way to avoid
sorting/aggregating over the whole table is for the outer query's WHERE
conditions on competition_name and academic_year_beginning to get pushed
down into the scans on challenge_entries ... and that can't happen if
there's a WITH in between.
2. Try increasing work_mem. I think that your first view would work all
right if it had enough work_mem to go for a HashAgg plan instead of
sort-and-group, even without pushdown of the outer WHERE. It'd
definitely be faster than what you've got, anyway.
The other approach with a window function is probably a lost cause.
Postgres hasn't got a lot of intelligence about optimizing
window-function queries ...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jdc41@cam.ac.uk
Subject: Re: Rapidly finding maximal rows
In-Reply-To: <13279.1318566353@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox