Re: sloooow query

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(at)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(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Josh Berkus
> Sent: Monday, October 07, 2002 2:29 PM
> To: marie(dot)tuite(at)edisonaffiliates(dot)com; pgsql-performance(at)postgresql(dot)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
>