X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4B662475F39 for ; Sat, 3 May 2003 22:30:26 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id A94D2475F39 for ; Sat, 3 May 2003 22:29:53 -0400 (EDT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3006558 for pgsql-performance@postgresql.org; Sat, 03 May 2003 19:29:22 -0700 Content-Type: text/plain; charset="us-ascii" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: PgSQL Performance ML Subject: Suggestions wanted for 7.2.4 query Date: Sat, 3 May 2003 19:28:52 -0700 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200305031928.52906.josh@agliodbs.com> X-Spam-Status: No, hits=-11.7 required=5.0 tests=BAYES_01,NO_DNS_FOR_FROM,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/36 X-Sequence-Number: 1815 Folks, I have a common query on a production database that's running a little too= =20 slow (3-6 seconds). I can currently drop the time to 0.8 seconds by=20 setting enable_seqscan =3D false; the main reason is the planner poorly=20 deciding to use a seq scan for the hash join between "events" and "cases",= =20 mostly due to a dramatically bad estimate of the number of rows required fr= om=20 "cases". Suggestions on how to get Postgres to use cases_pkey instead of a seq scan = on=20 cases without messing with global query settings in the database which migh= t=20 make other queries run slower? (And yes, a VACUUM FULL ANALYZE was=20 involved).=20=20 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 tgroup_nam= e, events.location_id, location_name, locations.zip_code, locations.address,= =20 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 =3D lock_record WHERE events.status <> 0 AND (events.status =3D status.status AND status.relation =3D 'events') 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; The Query: SELECT sv_events.*, FALSE AS fuzzy_team FROM sv_events WHERE EXISTS ( SELE= CT=20 event_id FROM event_days WHERE event_days.event_id =3D sv_events.event_id AND (event_day BETWEEN= =20 ('2003-04-08'::TIMESTAMP WITHOUT TIME ZONE) AND ('2003-06-17 23:59'::TIMESTAMP WITHOUT TIME ZONE) ) ); The Explain: jwnet_test=3D> \i perform.sql psql:perform.sql:9: NOTICE: QUERY PLAN: Limit (cost=3D199572.58..199572.58 rows=3D10 width=3D368) (actual=20 time=3D3239.95..3239.96 rows=3D10 loops=3D1) -> Sort (cost=3D199572.58..199572.58 rows=3D33575 width=3D368) (actual= =20 time=3D3239.92..3239.93 rows=3D41 loops=3D1) -> Hash Join (cost=3D6576.62..191013.53 rows=3D33575 width=3D368)= (actual=20 time=3D513.49..3220.38 rows=3D1790 loops=3D1) -> Hash Join (cost=3D6574.72..189924.26 rows=3D14837 width= =3D350)=20 (actual time=3D509.20..3063.85 rows=3D1790 loops=3D1) -> Hash Join (cost=3D38.81..180804.32 rows=3D14837=20 width=3D304) (actual time=3D16.38..452.80 rows=3D1919 loops=3D1) -> Hash Join (cost=3D33.92..180539.78 rows=3D14= 837=20 width=3D252) (actual time=3D15.68..428.38 rows=3D1919 loops=3D1) -> Hash Join (cost=3D22.17..180231.28=20 rows=3D14837 width=3D155) (actual time=3D13.98..406.61 rows=3D1919 loops=3D= 1) -> Seq Scan on events=20=20 (cost=3D0.00..179874.82 rows=3D14837 width=3D67) (actual time=3D0.27..382.4= 7=20 rows=3D1919 loops=3D1) SubPlan -> Index Scan using=20 event_days_pk on event_days (cost=3D0.00..6.01 rows=3D1 width=3D4) (actual= =20 time=3D0.01..0.01 rows=3D0 loops=3D29734) -> Hash (cost=3D21.99..21.99 rows= =3D72=20 width=3D83) (actual time=3D13.66..13.66 rows=3D0 loops=3D1) -> Subquery Scan lu=20=20 (cost=3D12.61..21.99 rows=3D72 width=3D83) (actual time=3D13.64..13.65 rows= =3D1=20 loops=3D1) -> Hash Join=20=20 (cost=3D12.61..21.99 rows=3D72 width=3D83) (actual time=3D13.63..13.64 rows= =3D1=20 loops=3D1) -> Seq Scan on=20 edit_locks (cost=3D0.00..7.94 rows=3D72 width=3D26) (actual time=3D12.82..= 12.83=20 rows=3D1 loops=3D1) -> Hash=20=20 (cost=3D6.50..6.50 rows=3D150 width=3D57) (actual time=3D0.71..0.71 rows=3D= 0 loops=3D1) -> Seq Scan = on=20 users (cost=3D0.00..6.50 rows=3D150 width=3D57) (actual time=3D0.01..0.47 = rows=3D150=20 loops=3D1) -> Hash (cost=3D11.00..11.00 rows=3D300= =20 width=3D97) (actual time=3D1.66..1.66 rows=3D0 loops=3D1) -> Seq Scan on locations=20=20 (cost=3D0.00..11.00 rows=3D300 width=3D97) (actual time=3D0.01..1.11 rows= =3D300=20 loops=3D1) -> Hash (cost=3D4.75..4.75 rows=3D56 width=3D52= ) (actual=20 time=3D0.60..0.60 rows=3D0 loops=3D1) -> Hash Join (cost=3D1.21..4.75 rows=3D56= =20 width=3D52) (actual time=3D0.17..0.51 rows=3D56 loops=3D1) -> Seq Scan on event_types=20=20 (cost=3D0.00..2.56 rows=3D56 width=3D31) (actual time=3D0.01..0.15 rows=3D5= 6 loops=3D1) -> Hash (cost=3D1.17..1.17 rows=3D1= 7=20 width=3D21) (actual time=3D0.07..0.07 rows=3D0 loops=3D1) -> Seq Scan on event_cats=20= =20 (cost=3D0.00..1.17 rows=3D17 width=3D21) (actual time=3D0.01..0.05 rows=3D1= 7 loops=3D1) -> Hash (cost=3D3800.07..3800.07 rows=3D112107 width= =3D46)=20 (actual time=3D491.84..491.84 rows=3D0 loops=3D1) -> Seq Scan on cases (cost=3D0.00..3800.07=20 rows=3D112107 width=3D46) (actual time=3D0.01..277.20 rows=3D112107 loops= =3D1) -> Hash (cost=3D1.88..1.88 rows=3D10 width=3D18) (actual=20 time=3D0.12..0.12 rows=3D0 loops=3D1) -> Seq Scan on status (cost=3D0.00..1.88 rows=3D10 wi= dth=3D18)=20 (actual time=3D0.03..0.11 rows=3D10 loops=3D1) Total runtime: 3241.09 msec The Index Scan: jwnet_test=3D> set enable_seqscan =3D false; SET VARIABLE jwnet_test=3D> \i perform.sql psql:perform.sql:9: NOTICE: QUERY PLAN: Limit (cost=3D252608.52..252608.52 rows=3D10 width=3D368) (actual=20 time=3D740.62..740.64 rows=3D10 loops=3D1) -> Sort (cost=3D252608.52..252608.52 rows=3D33469 width=3D368) (actual= =20 time=3D740.60..740.61 rows=3D41 loops=3D1) -> Hash Join (cost=3D86.85..244083.21 rows=3D33469 width=3D368) (= actual=20 time=3D20.93..720.70 rows=3D1790 loops=3D1) -> Hash Join (cost=3D80.75..242992.18 rows=3D14812 width=3D= 350)=20 (actual time=3D16.69..554.62 rows=3D1790 loops=3D1) -> Nested Loop (cost=3D49.20..242664.38 rows=3D14812= =20 width=3D253) (actual time=3D14.56..519.42 rows=3D1790 loops=3D1) -> Hash Join (cost=3D49.20..158631.12 rows=3D14= 812=20 width=3D207) (actual time=3D14.40..459.91 rows=3D1919 loops=3D1) -> Hash Join (cost=3D32.78..158355.48=20 rows=3D14812 width=3D155) (actual time=3D13.59..442.08 rows=3D1919 loops=3D= 1) -> Index Scan using idx_events_statu= s=20 on events (cost=3D0.00..157988.97 rows=3D14812 width=3D67) (actual=20 time=3D0.08..416.67 rows=3D1919 loops=3D1) SubPlan -> Index Scan using=20 event_days_pk on event_days (cost=3D0.00..5.26 rows=3D1 width=3D4) (actual= =20 time=3D0.01..0.01 rows=3D0 loops=3D29734) -> Hash (cost=3D32.60..32.60 rows= =3D72=20 width=3D83) (actual time=3D13.47..13.47 rows=3D0 loops=3D1) -> Subquery Scan lu=20=20 (cost=3D0.00..32.60 rows=3D72 width=3D83) (actual time=3D1.60..13.46 rows= =3D1 loops=3D1) -> Merge Join=20=20 (cost=3D0.00..32.60 rows=3D72 width=3D83) (actual time=3D1.59..13.45 rows= =3D1 loops=3D1) -> Index Scan usin= g=20 users_pkey on users (cost=3D0.00..19.63 rows=3D150 width=3D57) (actual=20 time=3D0.09..0.12 rows=3D3 loops=3D1) -> Index Scan usin= g=20 edit_locks_user_id on edit_locks (cost=3D0.00..11.51 rows=3D72 width=3D26)= (actual=20 time=3D1.43..13.28 rows=3D1 loops=3D1) -> Hash (cost=3D16.28..16.28 rows=3D56 wi= dth=3D52)=20 (actual time=3D0.77..0.77 rows=3D0 loops=3D1) -> Hash Join (cost=3D5.67..16.28 ro= ws=3D56=20 width=3D52) (actual time=3D0.29..0.68 rows=3D56 loops=3D1) -> Index Scan using=20 event_types_pkey on event_types (cost=3D0.00..9.63 rows=3D56 width=3D31) (= actual=20 time=3D0.08..0.28 rows=3D56 loops=3D1) -> Hash (cost=3D5.63..5.63 ro= ws=3D17=20 width=3D21) (actual time=3D0.15..0.15 rows=3D0 loops=3D1) -> Index Scan using=20 event_cats_pkey on event_cats (cost=3D0.00..5.63 rows=3D17 width=3D21) (ac= tual=20 time=3D0.08..0.13 rows=3D17 loops=3D1) -> Index Scan using cases_pkey on cases=20=20 (cost=3D0.00..5.66 rows=3D1 width=3D46) (actual time=3D0.02..0.02 rows=3D1 = loops=3D1919) -> Hash (cost=3D30.80..30.80 rows=3D300 width=3D97) (= actual=20 time=3D2.07..2.07 rows=3D0 loops=3D1) -> Index Scan using locations_pkey on locations= =20=20 (cost=3D0.00..30.80 rows=3D300 width=3D97) (actual time=3D0.09..1.61 rows= =3D300=20 loops=3D1) -> Hash (cost=3D6.07..6.07 rows=3D10 width=3D18) (actual=20 time=3D0.08..0.08 rows=3D0 loops=3D1) -> Index Scan using status_relation on status=20=20 (cost=3D0.00..6.07 rows=3D10 width=3D18) (actual time=3D0.03..0.06 rows=3D1= 0 loops=3D1) Total runtime: 741.72 msec --=20 -Josh Berkus Aglio Database Solutions San Francisco