pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Andrew Sullivan <andrew@libertyrms.info>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Suggestions wanted for 7.2.4 query
Date: Mon, 5 May 2003 12:27:25 -0700
Message-ID: <200305051227.25498.josh@agliodbs.com> (raw)
In-Reply-To: <20030505190201.GP20217@libertyrms.info>
References: <200305031928.52906.josh@agliodbs.com>
	<200305051059.11751.josh@agliodbs.com>
	<20030505190201.GP20217@libertyrms.info>

Andrew,

> > If anyone is interested, the above idea worked.
> 
> 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=> create function add_time ( timestamp without time zone, interval 
)
cal_test-> returns timestamp without time zone as '
cal_test'> select $1 + $2;
cal_test'> ' language 'sql' with (isstrict, iscachable);
cal_test=> create index idx_event_ends on events(add_time(event_date, 
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 rstatus, 
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 
show_date,
        cases.case_name || '(' || cases.docket || ')' as event_case,
        events.case_id, cases.case_name, cases.docket, NULL::VARCHAR as 
tgroup_name,
        events.location_id, location_name, locations.zip_code, 
locations.address, 
locations.state_code, locations.city,
        lu.user_name as lock_name, lu.email as lock_email, lu.user_id AS 
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 = lock_record
WHERE events.status <> 0
        AND (events.status = status.status AND status.relation = 'events')
        AND events.location_id = locations.location_id
        AND event_types.etype_id = events.etype_id
        AND event_cats.ecat_id = event_types.ecat_id
        AND events.case_id = 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 
('2003-05-19'::TIMESTAMP WITHOUT TIME ZONE)
 or sv_events.end_date BETWEEN ('2003-04-07'::TIMESTAMP WITHOUT TIME ZONE)  
AND ('2003-05-19'::TIMESTAMP WITHOUT TIME ZONE) )
  AND EXISTS ( SELECT event_id FROM event_days WHERE event_days.event_id = 
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 
trial_groups
    JOIN cases USING(tgroup_id)  WHERE trial_groups.status > 0 AND 
((UPPER(case_name)
    LIKE 'RODRIGUEZ%' OR  docket LIKE 'RODRIGUEZ%')  AND tgroup_id = 
sv_events.case_id)
    OR  (UPPER(tgroup_name) LIKE 'RODRIGUEZ%' AND cases.case_id = 
sv_events.case_id) ) ) AND rstatus <> 0;

The new version returns in 0.85 seconds, a 75% improvement!   Yahoo!

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




view thread (7+ messages)

Message-ID: <200305051227.25498.josh@agliodbs.com>
Permalink:  ../200305051227.25498.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200305051227.25498.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, andrew@libertyrms.info
  Subject: Re: Suggestions wanted for 7.2.4 query
  In-Reply-To: <200305051227.25498.josh@agliodbs.com>
  References: <200305031928.52906.josh@agliodbs.com> <200305051059.11751.josh@agliodbs.com> <20030505190201.GP20217@libertyrms.info> <200305051227.25498.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox