Skip site navigation (1) Skip section navigation (2)

Re: sloooow query

From: "Marie G(dot) Tuite" <marie(dot)tuite(at)edisonaffiliates(dot)com>
To: <josh(at)agliodbs(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: sloooow query
Date: 2002-10-07 19:49:16
Message-ID: IGELKLINGDMODABPOOFEMEBBCJAA.marie.tuite@edisonaffiliates.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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(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
>



In response to

Responses

pgsql-performance by date

Next:From: Marie G. TuiteDate: 2002-10-07 19:53:35
Subject: Re: sloooow query
Previous:From: Justin CliftDate: 2002-10-07 19:30:03
Subject: Re: sloooow query

pgsql-general by date

Next:From: Marie G. TuiteDate: 2002-10-07 19:53:35
Subject: Re: sloooow query
Previous:From: Thomas F.O'ConnellDate: 2002-10-07 19:38:47
Subject: news.postgresql.org

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group