Matching query plans with numerics changed to integers.
I sent the wrong query plans earlier
8.3.3: 1195 ms
8.2.12: 611 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.061..611.061
rows=0 loops=1)
-> Sort (cost=6986.01..6986.02 rows=1 width=20) (actual
time=611.053..611.053 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=955.90..6986.00 rows=1 width=20) (actual
time=610.942..610.942 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t6.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t8.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t10.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text,
'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=955.90..4989.27 rows=523 width=76)
(actual time=28.274..548.664 rows=1104 loops=1)
-> Hash Join (cost=955.90..3195.56 rows=526 width=52)
(actual time=28.123..520.620 rows=1104 loops=1)
Hash Cond: (t6.invoice_id = t7.id)
-> Seq Scan on payment t6 (cost=0.00..2002.69
rows=61789 width=16) (actual time=0.026..253.360 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=954.15..954.15 rows=140 width=44)
(actual time=25.795..25.795 rows=332 loops=1)
-> Nested Loop (cost=0.00..954.15 rows=140
width=44) (actual time=0.546..24.364 rows=332 loops=1)
-> Nested Loop (cost=0.00..562.62
rows=100 width=40) (actual time=0.471..15.759 rows=336 loops=1)
-> Nested Loop (cost=0.00..74.40
rows=156 width=28) (actual time=0.233..6.404 rows=376 loops=1)
-> Nested Loop
(cost=0.00..45.04 rows=4 width=20) (actual time=0.157..0.324 rows=4 loops=1)
-> Index Scan using
idx_department_du on department t10 (cost=0.00..7.30 rows=6 width=12) (actual
time=0.076..0.108 rows=7 loops=1)
Index Cond:
(company_id = 250893)
Filter: (active
<> 0)
-> Index Scan using
idx_project_department_id on project t2 (cost=0.00..6.28 rows=1 width=16)
(actual time=0.016..0.018 rows=1 loops=7)
Index Cond:
(t2.department_id = t10.id)
Filter: (active
<> 0)
-> Index Scan using
idx_project_type_project_id on project_type t11 (cost=0.00..6.09 rows=100
width=16) (actual time=0.030..0.817 rows=94 loops=4)
Index Cond: (t2.id =
t11.project_id)
Filter: (active <> 0)
-> Index Scan using
project_invoice_pkey on project_invoice t3 (cost=0.00..3.12 rows=1 width=12)
(actual time=0.007..0.011 rows=1 loops=376)
Index Cond: (t11.slot_id =
t3.id)
Filter: (active <> 0)
-> Index Scan using
idx_invoice_owner_resource_id on invoice t7 (cost=0.00..3.90 rows=1 width=16)
(actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t3.id =
t7.owner_resource_id)
Filter: (active <> 0)
-> Index Scan using idx_payment_amount_payment_id on
payment_amount t8 (cost=0.00..3.40 rows=1 width=28) (actual time=0.007..0.011
rows=1 loops=1104)
Index Cond: (t6.id = t8.payment_id)
-> Index Scan using amount_pkey on amount t9 (cost=0.00..3.77
rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t8.amount_id = t9.id)
Total runtime: 611.680 ms
(35 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6891.89..6891.90 rows=1 width=20) (actual
time=1194.811..1194.811 rows=0 loops=1)
-> Sort (cost=6891.89..6891.89 rows=1 width=20) (actual
time=1194.803..1194.803 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=3108.63..6891.88 rows=1 width=20) (actual
time=1194.733..1194.733 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t6.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t8.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t10.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text,
'YYYY/MM/DD HH:MI:SS'::text)))
-> Hash Join (cost=3108.63..4816.57 rows=571 width=76) (actual
time=799.114..1135.524 rows=1104 loops=1)
Hash Cond: (t8.payment_id = t6.id)
-> Seq Scan on payment_amount t8 (cost=0.00..1441.17
rows=69617 width=28) (actual time=0.042..263.815 rows=69617 loops=1)
-> Hash (cost=3101.44..3101.44 rows=575 width=52)
(actual time=593.227..593.227 rows=1104 loops=1)
-> Hash Join (cost=975.62..3101.44 rows=575
width=52) (actual time=98.739..588.389 rows=1104 loops=1)
Hash Cond: (t6.invoice_id = t7.id)
-> Seq Scan on payment t6
(cost=0.00..1891.69 rows=60901 width=16) (actual time=0.035..249.993 rows=60920
loops=1)
Filter: (active <> 0)
-> Hash (cost=973.66..973.66 rows=157
width=44) (actual time=96.404..96.404 rows=332 loops=1)
-> Nested Loop (cost=84.27..973.66
rows=157 width=44) (actual time=23.020..94.924 rows=332 loops=1)
-> Hash Join (cost=84.27..539.32
rows=120 width=40) (actual time=22.881..86.225 rows=336 loops=1)
Hash Cond: (t3.id =
t11.slot_id)
-> Seq Scan on
project_invoice t3 (cost=0.00..362.56 rows=9129 width=12) (actual
time=0.052..38.501 rows=8951 loops=1)
Filter: (active <> 0)
-> Hash (cost=81.97..81.97
rows=184 width=28) (actual time=13.379..13.379 rows=376 loops=1)
-> Nested Loop
(cost=0.00..81.97 rows=184 width=28) (actual time=3.188..11.716 rows=376
loops=1)
-> Nested Loop
(cost=0.00..51.98 rows=4 width=20) (actual time=0.309..0.499 rows=4 loops=1)
-> Index
Scan using idx_department_du on department t10 (cost=0.00..7.96 rows=7
width=12) (actual time=0.181..0.215 rows=7 loops=1)
Index Cond: (company_id = 250893)
Filter: (active <> 0)
-> Index
Scan using idx_project_department_id on project t2 (cost=0.00..6.28 rows=1
width=16) (actual time=0.024..0.027 rows=1 loops=7)
Index Cond: (t2.department_id = t10.id)
Filter: (t2.active <> 0)
-> Index Scan
using idx_project_type_project_id on project_type t11 (cost=0.00..6.16
rows=107 width=16) (actual time=0.729..2.100 rows=94 loops=4)
Index
Cond: (t11.project_id = t2.id)
Filter:
(t11.active <> 0)
-> Index Scan using
idx_invoice_owner_resource_id on invoice t7 (cost=0.00..3.61 rows=1 width=16)
(actual time=0.007..0.011 rows=1 loops=336)
Index Cond:
(t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0)
-> Index Scan using amount_pkey on amount t9 (cost=0.00..3.58
rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t9.id = t8.amount_id)
Total runtime: 1195.615 ms
(38 rows)
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
