On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <[email protected]>wrote:
> What happens if you do this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> EXPLAIN ANALYZE 8.3 query....
>
Right now, I'm getting very good results with the above. I'm still running
additional tests but I'll keep you guys updated. I've attached the new
explain analyze.
SET
SET
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=17397.17..17397.18 rows=2 width=20) (actual
time=621.865..621.865 rows=0 loops=1)
-> Sort (cost=17397.17..17397.18 rows=2 width=20) (actual
time=621.857..621.857 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..17397.16 rows=2 width=20) (actual
time=621.825..621.825 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=0.00..14615.82 rows=537 width=76) (actual
time=51.092..556.405 rows=1104 loops=1)
-> Nested Loop (cost=0.00..12007.84 rows=541 width=52)
(actual time=46.530..523.869 rows=1104 loops=1)
-> Nested Loop (cost=0.00..2229.06 rows=147
width=44) (actual time=42.642..501.168 rows=332 loops=1)
-> Nested Loop (cost=0.00..1530.56 rows=114
width=40) (actual time=39.485..489.224 rows=336 loops=1)
-> Nested Loop (cost=0.00..757.31
rows=177 width=28) (actual time=10.877..473.774 rows=376 loops=1)
-> Nested Loop (cost=0.00..69.07
rows=4 width=20) (actual time=6.463..6.640 rows=4 loops=1)
-> Index Scan using
idx_department_du on department t10 (cost=0.00..14.72 rows=7 width=12) (actual
time=6.141..6.173 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..7.26 rows=1 width=16)
(actual time=0.051..0.053 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..133.37 rows=77
width=16) (actual time=1.112..116.094 rows=94 loops=4)
Index Cond: (t11.project_id
= t2.id)
Filter: (t11.active <> 0)
-> Index Scan using
project_invoice_pkey on project_invoice t3 (cost=0.00..3.87 rows=1 width=12)
(actual time=0.024..0.027 rows=1 loops=376)
Index Cond: (t3.id = t11.slot_id)
Filter: (t3.active <> 0)
-> Index Scan using
idx_invoice_owner_resource_id on invoice t7 (cost=0.00..5.62 rows=1 width=16)
(actual time=0.017..0.021 rows=1 loops=336)
Index Cond: (t7.owner_resource_id =
t3.id)
Filter: (t7.active <> 0)
-> Index Scan using idx_payment_invoice_id on
payment t6 (cost=0.00..52.45 rows=28 width=16) (actual time=0.021..0.037
rows=3 loops=332)
Index Cond: (t6.invoice_id = t7.id)
Filter: (t6.active <> 0)
-> Index Scan using idx_payment_amount_payment_id on
payment_amount t8 (cost=0.00..4.32 rows=1 width=28) (actual time=0.011..0.015
rows=1 loops=1104)
Index Cond: (t8.payment_id = t6.id)
-> Index Scan using amount_pkey on amount t9 (cost=0.00..4.64
rows=1 width=12) (actual time=0.012..0.016 rows=1 loops=1104)
Index Cond: (t9.id = t8.amount_id)
Total runtime: 622.377 ms
(35 rows)
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
