Home > mailing lists

Optimizing this count query - Mailing list pgsql-performance

From	Andrei Bintintan
Subject	Optimizing this count query
Date	January 17, 2005 19:58:49
Msg-id	016801c4fcb5$cb36e8b0$0b00a8c0@forge Whole thread Raw
Responses	Re: Optimizing this count query
List	pgsql-performance

Tree view

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)

pgsql-performance by date:

From: Tom Lane
Date: 17 January 2005, 19:14:24
Subject: Re: Performance problem from migrating between versions!

From: William Yu
Date: 17 January 2005, 20:43:35
Subject: Re: Increasing RAM for more than 4 Gb. using postgresql

Optimizing this count query - Mailing list pgsql-performance

Previous

Next