Optimizing this count query
| От | Andrei Bintintan |
|---|---|
| Тема | Optimizing this count query |
| Дата | |
| Msg-id | 016801c4fcb5$cb36e8b0$0b00a8c0@forge обсуждение исходный текст |
| Ответы |
Re: Optimizing this count query
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-performance |
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
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)
-> 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 по дате отправления: