pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrei Bintintan <klodoma@ar-sd.net>
To: pgsql-performance@postgresql.org
Subject: Optimizing this count query
Date: Mon, 17 Jan 2005 18:58:09 +0200
Message-ID: <016801c4fcb5$cb36e8b0$0b00a8c0@forge> (raw)

Hi to all, 

I have a query which counts how many elements I have in the database.

SELECT count(o.id) FROM orders o
      INNER JOIN report r ON o.id=r.id_order
      INNER JOIN status s ON o.id_status=s.id
      INNER JOIN contact c ON o.id_ag=c.id
      INNER JOIN endkunde e ON o.id_endkunde=e.id
      INNER JOIN zufriden z ON r.id_zufriden=z.id
      INNER JOIN plannung v ON v.id=o.id_plannung
      INNER JOIN mpsworker w ON v.id_worker=w.id
      INNER JOIN person p ON p.id = w.id_person
      WHERE o.id_status>3 

In the tables are not quite so many rows (~ 100000).

I keep the joins because in the where clause there can be also other search elemens which are searched in the other tables. 
Now the id_status from the orders table (>3) can be 4 or 6. The id_status=6 has the most bigger percentage (4 = 10%, 6 = 70% and the rest are other statuses < 4). I think this is why the planner uses 

I'm asking how can I improve the execution time of this query, because these tables are always increasing. And this count sometimes takes more than 10 secs and I need to run this count very offen.

Best regards, 
Andy.


The explain:
Aggregate  (cost=37931.33..37931.33 rows=1 width=4)
  ->  Hash Join  (cost=27277.86..37828.45 rows=41154 width=4)
        Hash Cond: ("outer".id_person = "inner".id)
        ->  Hash Join  (cost=27269.79..37100.18 rows=41153 width=8)
              Hash Cond: ("outer".id_worker = "inner".id)
              ->  Hash Join  (cost=27268.28..36378.50 rows=41152 width=8)
                    Hash Cond: ("outer".id_endkunde = "inner".id)
                    ->  Hash Join  (cost=25759.54..33326.98 rows=41151 width=12)
                          Hash Cond: ("outer".id_ag = "inner".id)
                          ->  Hash Join  (cost=25587.07..32331.51 rows=41150 width=16)
                                Hash Cond: ("outer".id_status = "inner".id)
                                ->  Hash Join  (cost=25586.00..31713.18 rows=41150 width=20)
                                      Hash Cond: ("outer".id_zufriden = "inner".id)
                                      ->  Hash Join  (cost=25584.85..31094.78 rows=41150 width=24)
                                            Hash Cond: ("outer".id_plannung = "inner".id)
                                            ->  Hash Join  (cost=24135.60..27869.53 rows=41149 width=24)
                                                  Hash Cond: ("outer".id = "inner".id_order)
                                                  ->  Seq Scan on orders o  (cost=0.00..2058.54 rows=42527 width=20)
                                                        Filter: (id_status > 3)
                                                  ->  Hash  (cost=23860.48..23860.48 rows=42848 width=8)
                                                        ->  Seq Scan on report r  (cost=0.00..23860.48 rows=42848 width=8)
                                            ->  Hash  (cost=1050.80..1050.80 rows=62180 width=8)
                                                  ->  Seq Scan on plannung v  (cost=0.00..1050.80 rows=62180 width=8)
                                      ->  Hash  (cost=1.12..1.12 rows=12 width=4)
                                            ->  Seq Scan on zufriden z  (cost=0.00..1.12 rows=12 width=4)
                                ->  Hash  (cost=1.06..1.06 rows=6 width=4)
                                      ->  Seq Scan on status s  (cost=0.00..1.06 rows=6 width=4)
                          ->  Hash  (cost=161.57..161.57 rows=4357 width=4)
                                ->  Seq Scan on contact c  (cost=0.00..161.57 rows=4357 width=4)
                    ->  Hash  (cost=1245.99..1245.99 rows=44299 width=4)
                          ->  Seq Scan on endkunde e  (cost=0.00..1245.99 rows=44299 width=4)
              ->  Hash  (cost=1.41..1.41 rows=41 width=8)
                    ->  Seq Scan on mpsworker w  (cost=0.00..1.41 rows=41 width=8)
        ->  Hash  (cost=7.66..7.66 rows=166 width=4)
              ->  Seq Scan on person p  (cost=0.00..7.66 rows=166 width=4)

view thread (3+ messages)  latest in thread

Message-ID: <016801c4fcb5$cb36e8b0$0b00a8c0@forge>
Permalink:  ../016801c4fcb5$cb36e8b0$0b00a8c0@forge/
Also on:    postgresql.org/message-id/016801c4fcb5$cb36e8b0$0b00a8c0@forge

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: klodoma@ar-sd.net
  Subject: Re: Optimizing this count query
  In-Reply-To: <016801c4fcb5$cb36e8b0$0b00a8c0@forge>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox