pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Suggestions wanted for 7.2.4 query
7+ messages / 2 participants
[nested] [flat]

* Suggestions wanted for 7.2.4 query
@ 2003-05-04 02:28 Josh Berkus <josh@agliodbs.com>
  2003-05-04 04:42 ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2003-05-04 02:28 UTC (permalink / raw)
  To: pgsql-performance

Folks,

I have a common query on a production database that's running a little too 
slow (3-6 seconds).     I can currently drop the time to 0.8 seconds by 
setting enable_seqscan = false; the main reason is the planner poorly 
deciding to use a seq scan for the hash join between "events" and "cases", 
mostly due to a dramatically bad estimate of the number of rows required from 
"cases".

Suggestions on how to get Postgres to use cases_pkey instead of a seq scan on 
cases without messing with global query settings in the database which might 
make other queries run slower?  (And yes, a VACUUM FULL ANALYZE was 
involved).  

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
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;

The Query:
SELECT sv_events.*, FALSE AS fuzzy_team FROM sv_events WHERE  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) ) );


The Explain:
jwnet_test=> \i perform.sql
psql:perform.sql:9: NOTICE:  QUERY PLAN:

Limit  (cost=199572.58..199572.58 rows=10 width=368) (actual 
time=3239.95..3239.96 rows=10 loops=1)
  ->  Sort  (cost=199572.58..199572.58 rows=33575 width=368) (actual 
time=3239.92..3239.93 rows=41 loops=1)
        ->  Hash Join  (cost=6576.62..191013.53 rows=33575 width=368) (actual 
time=513.49..3220.38 rows=1790 loops=1)
              ->  Hash Join  (cost=6574.72..189924.26 rows=14837 width=350) 
(actual time=509.20..3063.85 rows=1790 loops=1)
                    ->  Hash Join  (cost=38.81..180804.32 rows=14837 
width=304) (actual time=16.38..452.80 rows=1919 loops=1)
                          ->  Hash Join  (cost=33.92..180539.78 rows=14837 
width=252) (actual time=15.68..428.38 rows=1919 loops=1)
                                ->  Hash Join  (cost=22.17..180231.28 
rows=14837 width=155) (actual time=13.98..406.61 rows=1919 loops=1)
                                      ->  Seq Scan on events  
(cost=0.00..179874.82 rows=14837 width=67) (actual time=0.27..382.47 
rows=1919 loops=1)
                                            SubPlan
                                              ->  Index Scan using 
event_days_pk on event_days  (cost=0.00..6.01 rows=1 width=4) (actual 
time=0.01..0.01 rows=0 loops=29734)
                                      ->  Hash  (cost=21.99..21.99 rows=72 
width=83) (actual time=13.66..13.66 rows=0 loops=1)
                                            ->  Subquery Scan lu  
(cost=12.61..21.99 rows=72 width=83) (actual time=13.64..13.65 rows=1 
loops=1)
                                                  ->  Hash Join  
(cost=12.61..21.99 rows=72 width=83) (actual time=13.63..13.64 rows=1 
loops=1)
                                                        ->  Seq Scan on 
edit_locks  (cost=0.00..7.94 rows=72 width=26) (actual time=12.82..12.83 
rows=1 loops=1)
                                                        ->  Hash  
(cost=6.50..6.50 rows=150 width=57) (actual time=0.71..0.71 rows=0 loops=1)
                                                              ->  Seq Scan on 
users  (cost=0.00..6.50 rows=150 width=57) (actual time=0.01..0.47 rows=150 
loops=1)
                                ->  Hash  (cost=11.00..11.00 rows=300 
width=97) (actual time=1.66..1.66 rows=0 loops=1)
                                      ->  Seq Scan on locations  
(cost=0.00..11.00 rows=300 width=97) (actual time=0.01..1.11 rows=300 
loops=1)
                          ->  Hash  (cost=4.75..4.75 rows=56 width=52) (actual 
time=0.60..0.60 rows=0 loops=1)
                                ->  Hash Join  (cost=1.21..4.75 rows=56 
width=52) (actual time=0.17..0.51 rows=56 loops=1)
                                      ->  Seq Scan on event_types  
(cost=0.00..2.56 rows=56 width=31) (actual time=0.01..0.15 rows=56 loops=1)
                                      ->  Hash  (cost=1.17..1.17 rows=17 
width=21) (actual time=0.07..0.07 rows=0 loops=1)
                                            ->  Seq Scan on event_cats  
(cost=0.00..1.17 rows=17 width=21) (actual time=0.01..0.05 rows=17 loops=1)
                    ->  Hash  (cost=3800.07..3800.07 rows=112107 width=46) 
(actual time=491.84..491.84 rows=0 loops=1)
                          ->  Seq Scan on cases  (cost=0.00..3800.07 
rows=112107 width=46) (actual time=0.01..277.20 rows=112107 loops=1)
              ->  Hash  (cost=1.88..1.88 rows=10 width=18) (actual 
time=0.12..0.12 rows=0 loops=1)
                    ->  Seq Scan on status  (cost=0.00..1.88 rows=10 width=18) 
(actual time=0.03..0.11 rows=10 loops=1)
Total runtime: 3241.09 msec

The Index Scan:
jwnet_test=> set enable_seqscan = false;
SET VARIABLE
jwnet_test=> \i perform.sql
psql:perform.sql:9: NOTICE:  QUERY PLAN:

Limit  (cost=252608.52..252608.52 rows=10 width=368) (actual 
time=740.62..740.64 rows=10 loops=1)
  ->  Sort  (cost=252608.52..252608.52 rows=33469 width=368) (actual 
time=740.60..740.61 rows=41 loops=1)
        ->  Hash Join  (cost=86.85..244083.21 rows=33469 width=368) (actual 
time=20.93..720.70 rows=1790 loops=1)
              ->  Hash Join  (cost=80.75..242992.18 rows=14812 width=350) 
(actual time=16.69..554.62 rows=1790 loops=1)
                    ->  Nested Loop  (cost=49.20..242664.38 rows=14812 
width=253) (actual time=14.56..519.42 rows=1790 loops=1)
                          ->  Hash Join  (cost=49.20..158631.12 rows=14812 
width=207) (actual time=14.40..459.91 rows=1919 loops=1)
                                ->  Hash Join  (cost=32.78..158355.48 
rows=14812 width=155) (actual time=13.59..442.08 rows=1919 loops=1)
                                      ->  Index Scan using idx_events_status 
on events  (cost=0.00..157988.97 rows=14812 width=67) (actual 
time=0.08..416.67 rows=1919 loops=1)
                                            SubPlan
                                              ->  Index Scan using 
event_days_pk on event_days  (cost=0.00..5.26 rows=1 width=4) (actual 
time=0.01..0.01 rows=0 loops=29734)
                                      ->  Hash  (cost=32.60..32.60 rows=72 
width=83) (actual time=13.47..13.47 rows=0 loops=1)
                                            ->  Subquery Scan lu  
(cost=0.00..32.60 rows=72 width=83) (actual time=1.60..13.46 rows=1 loops=1)
                                                  ->  Merge Join  
(cost=0.00..32.60 rows=72 width=83) (actual time=1.59..13.45 rows=1 loops=1)
                                                        ->  Index Scan using 
users_pkey on users  (cost=0.00..19.63 rows=150 width=57) (actual 
time=0.09..0.12 rows=3 loops=1)
                                                        ->  Index Scan using 
edit_locks_user_id on edit_locks  (cost=0.00..11.51 rows=72 width=26) (actual 
time=1.43..13.28 rows=1 loops=1)
                                ->  Hash  (cost=16.28..16.28 rows=56 width=52) 
(actual time=0.77..0.77 rows=0 loops=1)
                                      ->  Hash Join  (cost=5.67..16.28 rows=56 
width=52) (actual time=0.29..0.68 rows=56 loops=1)
                                            ->  Index Scan using 
event_types_pkey on event_types  (cost=0.00..9.63 rows=56 width=31) (actual 
time=0.08..0.28 rows=56 loops=1)
                                            ->  Hash  (cost=5.63..5.63 rows=17 
width=21) (actual time=0.15..0.15 rows=0 loops=1)
                                                  ->  Index Scan using 
event_cats_pkey on event_cats  (cost=0.00..5.63 rows=17 width=21) (actual 
time=0.08..0.13 rows=17 loops=1)
                          ->  Index Scan using cases_pkey on cases  
(cost=0.00..5.66 rows=1 width=46) (actual time=0.02..0.02 rows=1 loops=1919)
                    ->  Hash  (cost=30.80..30.80 rows=300 width=97) (actual 
time=2.07..2.07 rows=0 loops=1)
                          ->  Index Scan using locations_pkey on locations  
(cost=0.00..30.80 rows=300 width=97) (actual time=0.09..1.61 rows=300 
loops=1)
              ->  Hash  (cost=6.07..6.07 rows=10 width=18) (actual 
time=0.08..0.08 rows=0 loops=1)
                    ->  Index Scan using status_relation on status  
(cost=0.00..6.07 rows=10 width=18) (actual time=0.03..0.06 rows=10 loops=1)
Total runtime: 741.72 msec



-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Suggestions wanted for 7.2.4 query
  2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
@ 2003-05-04 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 16:07   ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2003-05-04 04:42 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> SELECT sv_events.*, FALSE AS fuzzy_team FROM sv_events WHERE  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) ) );

Is event_days.event_id unique?  If so, try

SELECT sv_events.*, FALSE AS fuzzy_team FROM sv_events, event_days
WHERE
event_days.event_id = sv_events.event_id AND
  (event_days.event_day BETWEEN 
('2003-04-08'::TIMESTAMP WITHOUT TIME ZONE)
  AND ('2003-06-17 23:59'::TIMESTAMP WITHOUT TIME ZONE) );

This at least gives you some glimmer of a chance that the restriction on
event_day can be used to avoid computing the entire join represented by
sv_events.  With the exists() form, there's no chance...

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Suggestions wanted for 7.2.4 query
  2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 04:42 ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-05-04 16:07   ` Josh Berkus <josh@agliodbs.com>
  2003-05-04 17:23     ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2003-05-04 16:07 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> > SELECT sv_events.*, FALSE AS fuzzy_team FROM sv_events WHERE  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) ) );
>
> Is event_days.event_id unique?  If so, try

Regrettably, no.   Event_days is an iterative list of all of the days covered 
by the event.   What's unique is event_days_pk, which is event_id, event_day.  
If I did a direct join to event_days, multi-day events would appear on the 
search results more than once .... which we *don't* want.

> This at least gives you some glimmer of a chance that the restriction on
> event_day can be used to avoid computing the entire join represented by
> sv_events.  With the exists() form, there's no chance...

Hmmm.  There are other ways I can get at the date limit for sv_events; I'll 
try that.  Unfortunately, those ways require a seq scan on events, so I'm not 
sure we have a net gain here (that is, I can't imagine that  a two-column 
date calculation between two parameters could be indexed)

 However, by my reading, 75% of the cost of the query is the unindexed join 
between "events" and "cases".    Are you saying that the planner being vague 
about what will be returned from the EXISTS clause is what's triggering the 
seq scan on "cases"?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Suggestions wanted for 7.2.4 query
  2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 04:42 ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 16:07   ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
@ 2003-05-04 17:23     ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 17:59       ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2003-05-04 17:23 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> This at least gives you some glimmer of a chance that the restriction on
>> event_day can be used to avoid computing the entire join represented by
>> sv_events.  With the exists() form, there's no chance...

> Hmmm.

I have to take that back (must have been out too late last night ;-)).
The EXISTS subquery *is* getting pushed down to become a restriction on
events alone; that's what the "SubPlan" is.  However, it'd still be
worth looking for another way to express it, because the planner is
pretty clueless about the selectivity of EXISTS restrictions.  That's
what's causing it to drastically overestimate the number of rows taken
from "events" (14812 vs 1919), which in turn drives it away from using
the nestloop-with-inner-indexscan join style for joining to "cases".

> Are you saying that the planner being vague about what will be
> returned from the EXISTS clause is what's triggering the seq scan on
> "cases"?

Right.  The nestloop/indexscan style only wins if there are not too many
outer rows.  If the EXISTS constraint actually did succeed for 14812
"events" rows, the planner would probably be making the right choice to
use a hash join.

BTW, have you tried lowering the value of "random_page_cost"?  Looking
at the relative costs in these examples makes me think most of your
tables are cached in memory.  Of course, if that's not true during
day-to-day production then you need to be wary about reducing the setting.

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Suggestions wanted for 7.2.4 query
  2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 04:42 ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 16:07   ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 17:23     ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-05-04 17:59       ` Josh Berkus <josh@agliodbs.com>
  2003-05-05 17:59         ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2003-05-04 17:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> I have to take that back (must have been out too late last night ;-)).
> The EXISTS subquery *is* getting pushed down to become a restriction on
> events alone; that's what the "SubPlan" is.  However, it'd still be
> worth looking for another way to express it, because the planner is
> pretty clueless about the selectivity of EXISTS restrictions.  That's
> what's causing it to drastically overestimate the number of rows taken
> from "events" (14812 vs 1919), which in turn drives it away from using
> the nestloop-with-inner-indexscan join style for joining to "cases".

That may be solvable without forcing a seq scan on "events", simply by 
overdetermining the criteria on date.  That is, I can't apply the date 
criteria to "events" because that would require running date calucations on 
each row forcing a seq scan ( i.e. (event_date + duration) between date_one 
and date_two would require a seq scan), but I can apply a broadend version of 
the criteria to "events" ( i.e. event_date between (date_one - 1 month) and 
(date_two + 1 day)) which would give the planner the idea that it is 
returning a minority of rows from "events".

Someday, we have to come up with a way of indexing simple multi-column 
calculations.   Unless someone did that in current source while I was behind 
on -hackers?

> Right.  The nestloop/indexscan style only wins if there are not too many
> outer rows.  If the EXISTS constraint actually did succeed for 14812
> "events" rows, the planner would probably be making the right choice to
> use a hash join.

Hmm.   Any hope of improving this in the future?  Like the IN() functionality 
improvements in 7.4?

> BTW, have you tried lowering the value of "random_page_cost"?  Looking
> at the relative costs in these examples makes me think most of your
> tables are cached in memory.  Of course, if that's not true during
> day-to-day production then you need to be wary about reducing the setting.

No, we're probably cached ... the machine has 1gb of RAM.   Also it has a 
really fast RAID array, at least for block disk reads, although random seek 
times suck.   I can tweak a little.   The problem is that it's a production 
machine in use 70 hours a week, so there isn't a lot of time we can test 
performance settings that might cause problems.

Thanks for the advice!

-- 
Josh Berkus
Aglio Database Solutions
San Francisco




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Suggestions wanted for 7.2.4 query
  2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 04:42 ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 16:07   ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 17:23     ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 17:59       ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
@ 2003-05-05 17:59         ` Josh Berkus <josh@agliodbs.com>
  2003-05-05 19:27           ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2003-05-05 17:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Folks,

> That may be solvable without forcing a seq scan on "events", simply by 
> overdetermining the criteria on date.  That is, I can't apply the date 
> criteria to "events" because that would require running date calucations on 
> each row forcing a seq scan ( i.e. (event_date + duration) between date_one 
> and date_two would require a seq scan), but I can apply a broadend version 
of 
> the criteria to "events" ( i.e. event_date between (date_one - 1 month) and 
> (date_two + 1 day)) which would give the planner the idea that it is 
> returning a minority of rows from "events".

If anyone is interested, the above idea worked.


-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Suggestions wanted for 7.2.4 query
  2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 04:42 ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 16:07   ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-04 17:23     ` Re: Suggestions wanted for 7.2.4 query Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-04 17:59       ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
  2003-05-05 17:59         ` Re: Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
@ 2003-05-05 19:27           ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2003-05-05 19:27 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2003-05-05 19:27 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-04 02:28 Suggestions wanted for 7.2.4 query Josh Berkus <josh@agliodbs.com>
2003-05-04 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-04 16:07   ` Josh Berkus <josh@agliodbs.com>
2003-05-04 17:23     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-04 17:59       ` Josh Berkus <josh@agliodbs.com>
2003-05-05 17:59         ` Josh Berkus <josh@agliodbs.com>
2003-05-05 19:27           ` Josh Berkus <josh@agliodbs.com>

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