Re: Rapidly finding maximal rows

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 time you
evaluate the select.

Our app has some similar queries (get me the most recent row from a data
logging table) and these work fine with a simple self-join, like this
example (irrelevant columns omitted for discussion)

select t.entity, t.time_stamp, t.data from log_table t
where t.entity=cast('21EC2020-3AEA-1069-A2DD-08002B30309D' as uuid)
and t.time_stamp=
(select max(time_stamp)
from log_table u
where t.entity=u.entity)

given a schema with the obvious indexes ...

create table log_table
(entity UUID,
time_stamp TIMESTAMP WITHOUT TIME ZONE,
data TEXT);

create index log_table_index on log_table (entity, time_stamp);

.. and the plan for the dependent sub-query does the obvious reverse index
scan as you'd expect / want.

If you still want / need to have the view, I suspect that getting rid of the
temp table definition will fix it ... my effort is below, alternatively you
might be able to take your first example and pull out best_scores and define
it as a view alos,

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.competition_name=ce2.competition_name
AND ce1.academic_year_beginning=ce2.academic_year_beginning
AND ce1.centre_number=ce2.centre_number
)

If you don't actually need to have the view for other purposes, and just
want to solve the original problem (listing certificates to be issued), you
can do it as a direct query, e.g.

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.competition_name=ce2.competition_name
AND ce1.academic_year_beginning=ce2.academic_year_beginning
AND ce1.centre_number=ce2.centre_number
)
AND competition_name = 'X'
AND academic_year_beginning = 2010

PostgreSQL also has a proprietary extension SELECT DISTINCT ON which has a
much nicer syntax, but unlike the above it will only show one (arbitrarily
selected) pupil per school in the event of a tie, which is probably not what
you want :-)

Looking at the schema, the constraint one_challenge_per_year is redundant
with the primary key.

Cheers
Dave

P.S. Small world ... did my undergrad there, back when @cam.ac.uk email went
to an IBM 3084 mainframe and the user ids typically ended in 10 :-)

On Tue, Oct 11, 2011 at 5:16 AM, James Cranch <jdc41(at)cam(dot)ac(dot)uk> wrote:

>
> 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 grateful for any suggestions. Reasonably full details are below.
>
>
> DESCRIPTION
> ===========
>
> I am writing a database which stores scores from mass-entry competitions
> ("challenges"). Candidates who were the best in their school (schools being
> identified by their "centre_number") receive a special certificate in each
> competition.
>
> Currently the database has data from six competitions, each entered by
> something like 200,000 students from about 2,000 schools. I wish to produce
> a view showing the students who had best-in-school performances.
>
> I have made two attempts (both immediately after running VACUUM ANALYZE),
> and both are surprisingly slow.
>
>
> THE QUERIES
> ===========
>
> I'm interested in running something like
>
> SELECT * FROM best_in_school_methodN WHERE competition_name = 'X' AND
> academic_year_beginning = 2010
>
> and the following two variants have been tried:
>
>
> CREATE VIEW best_in_school_method1 AS
> WITH best_scores(competition_name, academic_year_beginning,
> centre_number, total_score) AS
> (SELECT competition_name, academic_year_beginning, centre_number,
> MAX(total_score) AS total_score FROM challenge_entries GROUP BY
> competition_name, academic_year_beginning, centre_number)
> SELECT competition_name, academic_year_beginning, centre_number,
> entry_id, total_score, (true) AS best_in_school FROM challenge_entries
> NATURAL JOIN best_scores;
>
> This is EXPLAIN ANALYZEd here:
> http://explain.depesz.com/s/**EiS <http://explain.depesz.com/s/EiS>
>
>
> CREATE VIEW best_in_school_method2 AS
> WITH innertable(competition_name, academic_year_beginning, centre_number,
> entry_id, total_score, school_max_score) AS
> (SELECT competition_name, academic_year_beginning, centre_number,
> entry_id, total_score, MAX(total_score) OVER (PARTITION BY competition_name,
> academic_year_beginning, centre_number) AS centre_max_score FROM
> challenge_entries)
> SELECT competition_name, academic_year_beginning, centre_number,
> entry_id, total_score, (true) AS best_in_school FROM innertable WHERE
> centre_max_score = total_score;
>
> This one is EXPLAIN ANALYZEd here:
> http://explain.depesz.com/s/**6Eh <http://explain.depesz.com/s/6Eh>
>
>
> COMMENT
> =======
>
> In both cases, unless I've misunderstood, most of the time is taken up by
> sorting all the results for that particular competition. It appears to me
> that there should be much better ways: the results do not need to be fully
> sorted.
>
> If I were such an expert, I wouldn't be asking you all though.
>
> By the way, the choice to SELECT a value of true is so that I can join the
> results back into the original table to easily produce a best in school
> boolean column.
>
>
> SCHEMA
> ======
>
> I should explain the tables(though probably only the last one is
> interesting) and the index mentioned by one of the EXPLAINs. They can be
> produced by
>
> CREATE TABLE school_years
> (
> yearname VARCHAR(5) PRIMARY KEY,
> minimum_usual_age_september1 INTERVAL,
> maximum_usual_age_september1 INTERVAL,
> usual_successor VARCHAR(5) REFERENCES school_years
> );
>
> CREATE TABLE challenge_types
> (
> competition_name TEXT PRIMARY KEY,
> too_young_yeargroup VARCHAR(5) REFERENCES school_years
> );
>
> CREATE TABLE challenges
> (
> competition_name TEXT REFERENCES challenge_types,
> academic_year_beginning INTEGER,
> competition_date DATE,
> CONSTRAINT competition_is_in_year CHECK (competition_date BETWEEN (date
> (academic_year_beginning || '.001') + interval '9 months') AND (date
> (academic_year_beginning || '.001') + interval '21 months')),
> CONSTRAINT one_challenge_per_year UNIQUE (academic_year_beginning,**
> competition_name),
> PRIMARY KEY (competition_name, academic_year_beginning)
> );
>
> CREATE TABLE challenge_entries
> (
> entry_id SERIAL,
> competition_name TEXT,
> academic_year_beginning INTEGER,
> given_name TEXT,
> surname TEXT,
> centre_number CHAR(6),
> school_year VARCHAR(5),
> date_of_birth DATE,
> uk_educated BOOLEAN,
> uk_passport BOOLEAN,
> sex SEX,
> total_score INTEGER NOT NULL DEFAULT 0,
> PRIMARY KEY (competition_name,academic_**year_beginning,entry_id),
> FOREIGN KEY (school_year) REFERENCES school_years,
> FOREIGN KEY (competition_name,academic_**year_beginning) REFERENCES
> challenges );
>
> CREATE INDEX challenge_entries_by_**competition_centre_number_and_**
> total_score
> ON challenge_entries
> (competition_name,academic_**year_beginning,centre_number,**total_score
> DESC);
>
>
> SOFTWARE AND HARDWARE
> =====================
>
> I'm running "PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC
> gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit". It's the standard installation
> from Debian stable (Squeeze), and I haven't messed around with it.
>
> My Linux kernel is 2.6.32-5-amd64.
>
> I have a desktop PC with a Intel Core i7 CPU and 6GB of RAM, and a single
> 640GB Hitachi HDT72106 disk. My root partition is less than 30% full.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)**
> org <pgsql-performance(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance>
>