Re: [SQL] Query optimizing

[Richard Huxton]

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;

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql