On Tue, Oct 11, 2011 at 8:05 PM, Dave Crooke wrote:
> Hi James
>
>
> I'm guessing the problem is that the combination of using a view and the way
> the view is defined with an in-line temporary table is too complex for the
> planner to introspect into, transform and figure out the equivalent direc
James Cranch 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 ar
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.
DESCRIPTION
===
Dear Bricklen,
Try setting work_mem to something larger, like 40MB to do that sort
step in memory, rather than spilling to disk. The usual caveats apply
though, like if you have many users/queries performing sorts or
aggregations, up to that amount of work_mem may be used at each step
potentiall
Dear Dave,
CREATE VIEW best_in_school_method3 AS
SELECT competition_name, academic_year_beginning, centre_number,
entry_id, total_score, (true) AS best_in_school FROM challenge_entries
ce1
WHERE total_score =
(SELECT MAX(total_score) FROM challenge_entries ce2
WHERE ce1.competiti
Hi James
I'm guessing the problem is that the combination of using a view and the way
the view is defined with an in-line temporary table is too complex for the
planner to introspect into, transform and figure out the equivalent direct
query, and so it's creating that entire temporary table every
On Tue, Oct 11, 2011 at 3:16 AM, James Cranch wrote:
>
> This is EXPLAIN ANALYZEd here:
> http://explain.depesz.com/s/EiS
"Sort Method: external merge Disk: 35712kB"
>
> SOFTWARE AND HARDWARE
> =
>
> I'm running "PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC
> gc
I have a slow query, based on the problem of finding the set of rows which
are maximal in some sense. 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
