X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 68134475EEE for ; Mon, 5 May 2003 15:27:56 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 4F321475E82 for ; Mon, 5 May 2003 15:27:55 -0400 (EDT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3009888; Mon, 05 May 2003 12:27:55 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Andrew Sullivan Subject: Re: Suggestions wanted for 7.2.4 query Date: Mon, 5 May 2003 12:27:25 -0700 User-Agent: KMail/1.4.3 References: <200305031928.52906.josh@agliodbs.com> <200305051059.11751.josh@agliodbs.com> <20030505190201.GP20217@libertyrms.info> In-Reply-To: <20030505190201.GP20217@libertyrms.info> Cc: pgsql-performance MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200305051227.25498.josh@agliodbs.com> X-Spam-Status: No, hits=-23.3 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTE_TWICE_1,REFERENCES, USER_AGENT_KMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/50 X-Sequence-Number: 1829 Andrew, > > If anyone is interested, the above idea worked. >=20 > I am. Thanks, that was a clever idea. Thanks! In that case, I'll give you the full implementation: 1) Build an index on the product of time and duration for the table "events= ": jwnet_test=3D> create function add_time ( timestamp without time zone, inte= rval=20 ) cal_test-> returns timestamp without time zone as ' cal_test'> select $1 + $2; cal_test'> ' language 'sql' with (isstrict, iscachable); cal_test=3D> create index idx_event_ends on events(add_time(event_date,=20 duration)); CREATE 2) add this as a column to the view: create view sv_events as select events.event_id, events.status, status_label, status.rollup as rstat= us,=20 events.etype_id, type_name, event_cats.ecat_id, cat_name, events.event_date, events.event_name, jw_date_format(events.event_date, events.event_tz, events.duration)= as=20 show_date, cases.case_name || '(' || cases.docket || ')' as event_case, events.case_id, cases.case_name, cases.docket, NULL::VARCHAR as=20 tgroup_name, events.location_id, location_name, locations.zip_code,=20 locations.address,=20 locations.state_code, locations.city, lu.user_name as lock_name, lu.email as lock_email, lu.user_id AS=20 lock_user, add_time(events.event_date, events.duration) as end_date FROM status, locations, event_types, event_cats, cases, events LEFT OUTER JOIN lock_users lu ON events.event_id =3D lock_re= cord WHERE events.status <> 0 AND (events.status =3D status.status AND status.relation =3D 'event= s') AND events.location_id =3D locations.location_id AND event_types.etype_id =3D events.etype_id AND event_cats.ecat_id =3D event_types.ecat_id AND events.case_id =3D cases.case_id; 3) change the query as follows: SELECT sv_events.*, FALSE AS fuzzy_team FROM sv_events WHERE (sv_events.event_date BETWEEN ('2003-04-07'::TIMESTAMP WITHOUT TIME ZONE) = AND=20 ('2003-05-19'::TIMESTAMP WITHOUT TIME ZONE) or sv_events.end_date BETWEEN ('2003-04-07'::TIMESTAMP WITHOUT TIME ZONE)= =20=20 AND ('2003-05-19'::TIMESTAMP WITHOUT TIME ZONE) ) AND EXISTS ( SELECT event_id FROM event_days WHERE event_days.event_id = =3D=20 sv_events.event_id AND (event_day BETWEEN ('2003-04-08'::TIMESTAMP WITHOUT TIME ZONE) AND ('2003-06-17 23:59'::TIMESTAMP WITHOUT TIME ZONE) ) ) AND ( UPPER(case_name) LIKE 'RODRIGUEZ%' OR docket LIKE 'RODRIGUEZ%' OR UPPER(tgroup_name) LIKE 'RODRIGUEZ%' OR EXISTS (SELECT tgroup_id FROM= =20 trial_groups JOIN cases USING(tgroup_id) WHERE trial_groups.status > 0 AND=20 ((UPPER(case_name) LIKE 'RODRIGUEZ%' OR docket LIKE 'RODRIGUEZ%') AND tgroup_id =3D=20 sv_events.case_id) OR (UPPER(tgroup_name) LIKE 'RODRIGUEZ%' AND cases.case_id =3D=20 sv_events.case_id) ) ) AND rstatus <> 0; The new version returns in 0.85 seconds, a 75% improvement! Yahoo! --=20 -Josh Berkus Aglio Database Solutions San Francisco