Re: Query optimizing - Mailing list pgsql-sql

Richard Huxton wrote:
> Do you have an index on (id,dt_modified) for manage_followup? Can you
>  provide an EXPLAIN ANALYSE for this?

> Hi Richard,
>
> Firstly, thank-you very much for your swift reply. To answer your
> question,
> I had not been using an index on dt_modfied. I have added it now and
> ran explain analyse on the function snippet. I am almost too
> embarrassed
to show
> the result....
>
> QUERY PLAN
[snip]
> Total runtime: 412464.804 ms!!!!

Something wrong here. I've attacked a small script that generates 10,000
issues and 10 follow-ups for each. It then pulls off the most recent
follow-ups for all issues occurring on a given date.

The explain analyse should show both indexes being used and a runtime of
a few milliseconds.

--
  Richard Huxton
  Archonet Ltd
BEGIN;

CREATE SCHEMA issuefup;

SET search_path = issuefup;

CREATE TABLE issues (
    id integer,
    dt_created timestamp(0) with time zone,
    dt_modified timestamp(0) with time zone,
    t_title varchar(100),
    t_description text
);

CREATE TABLE followups (
    id integer,
    dt_created timestamp(0) with time zone,
    dt_modified timestamp(0) with time zone,
    t_description text,
    n_issue integer NOT NULL
);

INSERT INTO issues
    (id, dt_created, dt_modified, t_title, t_description)
SELECT
    (d*100 + i),
    '2008-01-01'::date + (d * '1 day'::interval),
    '2008-01-01'::date + (d * '1 day'::interval),
    'issue title ' || d || '/' || i,
    'issue description ' || d || '/' || i
FROM
    generate_series(0,99) AS d,
    generate_series(0,99) AS i
;


INSERT INTO followups
    (id, dt_created, dt_modified, t_description, n_issue)
SELECT
    (i.id * 10) + d,
    '2008-01-01'::date + ((i.id + d) * '1 day'::interval),
    '2008-01-01'::date + ((i.id + d) * '1 day'::interval),
    'followup description ' || ((i.id * 10) + d),
    i.id
FROM
    generate_series(0,9) AS d,
    issues AS i
;

ALTER TABLE issues ADD  PRIMARY KEY (id);
ALTER TABLE followups ADD PRIMARY KEY (id);
ALTER TABLE followups ADD CONSTRAINT n_issue_fkey FOREIGN KEY (n_issue) REFERENCES issues (id);
CREATE INDEX issues_dt_idx ON issues (dt_modified);
CREATE INDEX followups_nissue_dt_idx ON followups (n_issue, dt_modified);

ANALYSE ;

EXPLAIN ANALYSE
SELECT
    fu.*
FROM
    issues i,
    followups fu
WHERE
    i.dt_modified = '2008-01-07 00:00:00+00'
    AND fu.id = (
        SELECT f.id
        FROM followups f
        WHERE f.n_issue = i.id
        ORDER BY f.dt_modified DESC LIMIT 1
    )
;

ROLLBACK;