Re: sloooow query - Mailing list pgsql-performance
From | Marie G. Tuite |
---|---|
Subject | Re: sloooow query |
Date | |
Msg-id | IGELKLINGDMODABPOOFEMEBBCJAA.marie.tuite@edisonaffiliates.com Whole thread Raw |
In response to | Re: sloooow query (Josh Berkus <josh@agliodbs.com>) |
Responses |
Re: sloooow query
Re: sloooow query |
List | pgsql-performance |
Josh, Thanks for the reply. I pg_dumped the first database having performance problems and reloaded it into a new database on the same server. The query ran normally when I reloaded it. There is no difference in hardware, schema or anything else. project=# select version(); version ------------------------------------------------------------- PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) [mtuite@area52 mtuite]$ uname -a Linux area52.spacedock.com 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown Below is the explain for the reload. bm221=# \i bad.qry psql:bad.qry:78: NOTICE: QUERY PLAN: Sort (cost=273.71..273.71 rows=1 width=237) (actual time=143.82..143.96 rows=181 loops=1) -> Group (cost=273.53..273.70 rows=1 width=237) (actual time=136.98..140.78 rows=181 loops=1) -> Sort (cost=273.53..273.53 rows=7 width=237) (actual time=136.95..137.11 rows=181 loops=1) -> Merge Join (cost=273.37..273.43 rows=7 width=237) (actual time=124.41..129.72 rows=181 loops=1) -> Sort (cost=162.24..162.24 rows=7 width=216) (actual time=51.83..52.00 rows=181 loops=1) -> Subquery Scan student_set (cost=161.09..162.14 rows=7 width=216) (actual time=48.12..50.49 rows=181 loops=1) -> Unique (cost=161.09..162.14 rows=7 width=216) (actual time=48.10..49.45 rows=181 loops=1) -> Sort (cost=161.09..161.09 rows=70 width=216) (actual time=48.09..48.26 rows=181 loops=1) -> Hash Join (cost=130.58..158.96 rows=70 width=216) (actual time=43.26..47.11 rows=181 loops=1) -> Seq Scan on classes c (cost=0.00..20.00 rows=1000 width=72) (actual time=0.12..1.78 rows=332 loops=1) -> Hash (cost=130.55..130.55 rows=14 width=144) (actual time=43.02..43.02 rows=0 loops=1) -> Hash Join (cost=105.38..130.55 rows=14 width=144) (actual time=31.13..42.44 rows=181 loops=1) -> Seq Scan on user_common uc (cost=0.00..20.00 rows=1000 width=80) (actual time=0.12..7.07 rows=1045 loops=1) -> Hash (cost=105.37..105.37 rows=3 width=64) (actual time=30.91..30.91 rows=0 loops=1) -> Hash Join (cost=77.46..105.37 rows=3 width=64) (actual time=4.79..30.46 rows=181 loops=1) -> Seq Scan on student_class_rlt scr (cost=0.00..22.50 rows=995 width=24) (actual time=0.25..23.74 rows=527 loops=1) -> Hash (cost=77.45..77.45 rows=5 width=40) (actual time=4.02..4.02 rows=0 loops=1) -> Hash Join (cost=52.38..77.45 rows=5 width=40) (actual time=3.28..3.96 rows=27 loops=1) -> Seq Scan on bm_subscriptions_rlt bsr (cost=0.00..20.00 rows=1000 width=8) (actual time=0.11..0.47 rows=114 loops=1) -> Hash (cost=52.38..52.38 rows=1 width=32) (actual time=3.10..3.10 rows=0 loops=1) -> Hash Join (cost=4.83..52.38 rows=1 width=32) (actual time=2.23..3.07 rows=11 loops=1) -> Seq Scan on bm_publications bp (cost=0.00..47.50 rows=11 width=12) (actual time=1.49..2.25 rows=11 loops=1) -> Hash (cost=4.82..4.82 rows=1 width=20) (actual time=0.63..0.63 rows=0 loops=1) -> Index Scan using bm_publication_events_pkey on bm_publication_events bpe (cost=0.00..4.82 rows=1 width=20) (actual time=0.60..0.61 rows=1 loops=1) -> Sort (cost=111.13..111.13 rows=18 width=21) (actual time=72.51..73.15 rows=770 loops=1) -> Subquery Scan participation_set (cost=22.51..110.75 rows=18 width=21) (actual time=1.32..57.28 rows=809 loops=1) -> Hash Join (cost=22.51..110.75 rows=18 width=21) (actual time=1.30..52.21 rows=809 loops=1) -> Seq Scan on bm_user_results bur (cost=0.00..70.01 rows=3601 width=17) (actual time=0.14..18.53 rows=3601 loops=1) -> Hash (cost=22.50..22.50 rows=5 width=4) (actual time=0.91..0.91 rows=0 loops=1) -> Seq Scan on bm_publications bp (cost=0.00..22.50 rows=5 width=4) (actual time=0.33..0.71 rows=98 loops=1) Total runtime: 145.69 msec EXPLAIN bm221=# Here is the explain from the original database: project=# \i bad.qry psql:bad.qry:78: NOTICE: QUERY PLAN: Sort (cost=337.23..337.23 rows=1 width=237) (actual time=14903.87..14904.05 rows=181 loops=1) -> Group (cost=337.19..337.22 rows=1 width=237) (actual time=14895.90..14900.55 rows=181 loops=1) -> Sort (cost=337.19..337.19 rows=1 width=237) (actual time=14895.87..14896.09 rows=181 loops=1) -> Nested Loop (cost=214.62..337.18 rows=1 width=237) (actual time=149.50..14886.63 rows=181 loops=1) -> Subquery Scan student_set (cost=208.82..208.84 rows=1 width=115) (actual time=64.03..69.44 rows=181 loops=1) -> Unique (cost=208.82..208.84 rows=1 width=115) (actual time=64.02..67.25 rows=181 loops=1) -> Sort (cost=208.82..208.82 rows=1 width=115) (actual time=64.01..64.36 rows=181 loops=1) -> Nested Loop (cost=16.54..208.81 rows=1 width=115) (actual time=5.21..62.66 rows=181 loops=1) -> Nested Loop (cost=16.54..203.55 rows=1 width=88) (actual time=5.11..52.60 rows=181 loops=1) -> Hash Join (cost=16.54..197.63 rows=1 width=64) (actual time=4.55..37.75 rows=181 loops=1) -> Seq Scan on student_class_rlt scr (cost=0.00..178.16 rows=574 width=24) (actual time=0.02..29.59 rows=527 loops=1) -> Hash (cost=16.54..16.54 rows=2 width=40) (actual time=3.84..3.84 rows=0 loops=1) -> Hash Join (cost=13.80..16.54 rows=2 width=40) (actual time=2.91..3.77 rows=27 loops=1) -> Seq Scan on bm_subscriptions_rlt bsr (cost=0.00..2.14 rows=114 width=8) (actual time=0.01..0.50 rows=114 loops=1) -> Hash (cost=13.80..13.80 rows=2 width=32) (actual time=2.81..2.81 rows=0 loops=1) -> Hash Join (cost=1.06..13.80 rows=2 width=32) (actual time=1.74..2.78 rows=11 loops=1) -> Seq Scan on bm_publications bp (cost=0.00..12.65 rows=11 width=12) (actual time=1.56..2.51 rows=11 loops=1) -> Hash (cost=1.06..1.06 rows=1 width=20) (actual time=0.06..0.06 rows=0 loops=1) -> Seq Scan on bm_publication_events bpe (cost=0.00..1.06 rows=1 width=20) (actual time=0.04..0.05 rows=1 loops=1) -> Index Scan using user_common_pkey on user_common uc (cost=0.00..5.90 rows=1 width=24) (actual time=0.05..0.06 rows=1 loops=181) -> Index Scan using class_pkey on classes c (cost=0.00..5.25 rows=1 width=27) (actual time=0.03..0.04 rows=1 loops=181) -> Subquery Scan participation_set (cost=5.79..109.63 rows=1248 width=21) (actual time=1.19..78.18 rows=816 loops=181) -> Hash Join (cost=5.79..109.63 rows=1248 width=21) (actual time=1.18..71.10 rows=816 loops=181) -> Seq Scan on bm_user_results bur (cost=0.00..70.16 rows=3616 width=17) (actual time=0.01..20.96 rows=3620 loops=181) -> Hash (cost=5.55..5.55 rows=98 width=4) (actual time=1.05..1.05 rows=0 loops=181) -> Seq Scan on bm_publications bp (cost=0.00..5.55 rows=98 width=4) (actual time=0.33..0.82 rows=98 loops=181) Total runtime: 14905.87 msec EXPLAIN project=# Here is the query: explain analyze select student_set.pub_id as pub_id, student_set.class_id as class, student_set.class_name as class_name, student_set.user_id as student, student_set.first_name, student_set.last_name, participation_set.started, participation_set.complete, day,month from ( select distinct scr.user_id, scr.class_id, uc.first_name, uc.last_name, bp.bm_publication_id as pub_id, c.class_name from student_class_rlt scr, user_common uc, bm_subscriptions_rlt bsr, bm_publications bp CROSS JOIN bm_publication_events bpe, classes c where bpe.bm_publication_event_id = 4 and bpe.bm_publication_event_id = bp.bm_publication_event_id and bp.bm_series_id = bsr.bm_series_id and bsr.class_id = scr.class_id and scr.class_id = c.class_id and (scr.end_date is null or scr.end_date >= bpe.due_date) and scr.start_date <= bpe.publication_date and scr.status_id != 2 and scr.user_id = uc.user_id and bp.bm_publication_id in ( 4,25,1,3,26,19, ,11,27,90,20,28 ) ) student_set left join ( select user_id, initial_timestmp as started, to_char( initial_timestmp, 'MM/DD' ) as day, to_char( initial_timestmp, 'Month YYYY' ) as month, complete, bur.bm_publication_id as pub_id from bm_publications bp, bm_user_results bur where bp.bm_publication_event_id = 4 and bp.bm_publication_id = bur.bm_publication_id ) participation_set on ( student_set.user_id = participation_set.user_id and student_set.pub_id = participation_set.pub_id ) group by student_set.pub_id, class, class_name, student, last_name, first_name, started, complete, day, month order by student_set.pub_id, class, last_name, month, day ; Thanks. > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Josh Berkus > Sent: Monday, October 07, 2002 2:29 PM > To: marie.tuite@edisonaffiliates.com; pgsql-performance@postgresql.org > Subject: Re: [pgsql-performance] sloooow query > > > > Marie, > > > I am experiencing slow db performance. I have vacuumed, > analyzed, reindexed > > using the force option and performance remains the same - > dog-slow :( If I > > drop and recreate the database, performance is normal, so this > suggests a > > problem with the indexes? I also took a look at the > postgresql.conf and all > > appears fine. There are many instances of the same database running on > > different servers and not all servers are experiencing the problem. > > Please post the following: > 1) A copy of the relevant portions of your database schema. > 2) The query that is running slowly. > 3) The results of running EXPLAIN on that query. > 4) Your PostgreSQL version and operating system > 5) Any other relevant information about your databases, such as > the quantity > of inserts and deletes on the relevant tables. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
pgsql-performance by date: