Thread: Suggestions wanted for 7.2.4 query
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
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
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
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
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
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
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