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 (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
