help-simplify query - Mailing list pgsql-sql

Dear All<br />I need to simplify this query, It takes a lot of time to execute<br />Since "skala_rental" table has
2.3millionrows(Table Size:387 MB, Indexes Size: 132MB) and scanned 6 times in the execution. cf_application_id has
+100000rows,Table Size: 21 MB, Indexes Size: 18MB<br /> I guest the problem is on this subquery<br />sr.counter
=(SELECTMIN(counter)<br />    FROM skala_rental<br />    WHERE cf_application_id = cf.cf_application_id<br />    AND
(payment_dateIS NULL OR payment_date > '2008/1/1') AND counter <> 0)<br /> This part is really slow in
execution<br/>and there are redundant "WHERE clause"/Filters between subqueries<br />I cannot provide my server
configuarion,i have no access there<br />Is there any idea/suggestion to make this better query?<br /> Please help me
tofind the better one<br /><br />---------->This is my query that need to simplfy<br />SELECT<br />--principle_1
2009<br/>COALESCE ((SELECT SUM(principle)<br />  FROM skala_rental<br />  WHERE cf_application_id =
cf.cf_application_id<br/>     AND (payment_date IS NULL OR payment_date > '2008/1/1')<br />    AND counter <>
0<br/>    AND due_date <= DATEADD('year',1,'2008/1/1')), 0) AS principle_1,<br />--interest_1 2009<br />COALESCE
((SELECTSUM(interest)<br />   FROM skala_rental<br />  WHERE cf_application_id = cf.cf_application_id<br />    AND
(payment_dateIS NULL OR payment_date > '2008/1/1')<br />    AND counter <> 0<br />    AND due_date <=
DATEADD('year',1,'2008/1/1')),0) AS interest_1,<br /><br />--principle_2 2010<br />COALESCE ((SELECT SUM(principle)<br
/> FROM skala_rental<br />  WHERE cf_application_id = cf.cf_application_id<br />    AND due_date BETWEEN
DATEADD('day',1,DATEADD('year',1,'2008/1/1'))<br/>     AND DATEADD('year',2,'2008/1/1')),0) AS principle_2,<br
/>--interest_22010<br />COALESCE ((SELECT SUM(interest)<br />  FROM skala_rental<br />  WHERE cf_application_id =
cf.cf_application_id<br/>    AND due_date BETWEEN DATEADD('day',1,DATEADD('year',1,'2008/1/1'))<br />     AND
DATEADD('year',2,'2008/1/1')),0)AS interest_2<br />FROM<br />  cf_application cf<br />  JOIN skala_rental sr ON
sr.cf_application_id= cf.cf_application_id<br />WHERE 1=1<br />  AND cf.drawdown_date <= '2008/1/1'<br />   AND
(cf.terminate_dateIS NULL OR cf.terminate_date > '2008/1/1')<br />  AND sr.counter =<br />   (SELECT MIN(counter)<br
/>   FROM skala_rental<br />    WHERE cf_application_id = cf.cf_application_id<br />    AND (payment_date IS NULL OR
payment_date> '2008/1/1')<br />     AND counter <> 0)<br />  AND cf.branch_id = 2<br />ORDER BY
cf.cf_drawdown_number<br/><br /><br />----------> This is skala_rental & cf_application schema,<br />some
unecessaryfields were removed<br />CREATE TABLE skala_rental<br /> ( skala_rental_id bigint NOT NULL, counter integer
NOTNULL,<br />  due_date date NOT NULL, payment_date date,<br />  rental double precision NOT NULL, principle double
precisionNOT NULL,<br />  interest double precision NOT NULL, cf_application_id bigint,<br />   CONSTRAINT
skala_rental_pkeyPRIMARY KEY (skala_rental_id)<br />) WITH (OIDS=FALSE);<br /><br />CREATE INDEX ix_skala_rental ON
skala_rental<br/>  USING btree (cf_application_id, counter, payment_date, due_date);<br /><br /><br />CREATE TABLE
cf_application<br/> ( cf_application_id bigint NOT NULL, cf_application_number character varying(32),<br /> 
old_cf_application_numbercharacter varying(32), organization_id bigint,<br />  branch_id bigint NOT NULL, drawdown_date
date,<br/>  terminate_date date, last_due_date date,<br />   CONSTRAINT cf_application_pkey PRIMARY KEY
(cf_application_id),<br/>  CONSTRAINT fk9889f3744997923b FOREIGN KEY (record_status_id)<br />      REFERENCES status
(status_id)MATCH SIMPLE<br />      ON UPDATE NO ACTION ON DELETE NO ACTION<br /> ) WITH (OIDS=FALSE);<br /><br />CREATE
INDEXix_cf_application ON cf_application<br />  USING btree (cf_application_number, cf_drawdown_number,
application_date,corporate_id, personal_id, branch_id, record_status_id, marketing_id, product_id, cf_application_id,
old_cf_application_number,drawdown_date, terminate_date, organization_id);<br /><br /><br />----------> The Explain
Analyze:<br/>"Sort  (cost=1185871.07..1185871.81 rows=297 width=29) (actual time=24472.933..24474.260 rows=1770
loops=1)"<br/>"  Sort Key: cf.cf_drawdown_number"<br />"  Sort Method:  quicksort  Memory: 268kB"<br /> "  -> 
NestedLoop  (cost=3.26..1185858.87 rows=297 width=29) (actual time=33.743..24460.729 rows=1770 loops=1)"<br />"       
-> Seq Scan on cf_application cf  (cost=0.00..4557.67 rows=2750 width=29) (actual time=15.873..82.538 rows=1770
loops=1)"<br/> "              Filter: ((drawdown_date <= '2008-01-01'::date) AND ((terminate_date IS NULL) OR
(terminate_date> '2008-01-01'::date)) AND (branch_id = 2))"<br />"        ->  Index Scan using ix_skala_rental on
skala_rentalsr  (cost=3.26..18.79 rows=6 width=12) (actual time=0.008..0.009 rows=1 loops=1770)"<br /> "             
IndexCond: ((sr.cf_application_id = cf.cf_application_id) AND (sr.counter = (subplan)))"<br />"             
SubPlan"<br/>"                ->  Result  (cost=3.25..3.26 rows=1 width=0) (actual time=0.030..0.031 rows=1
loops=1770)"<br/> "                      InitPlan"<br />"                        ->  Limit  (cost=0.00..3.25 rows=1
width=4)(actual time=0.025..0.026 rows=1 loops=1770)"<br />"                              ->  Index Scan using
ix_skala_rentalon skala_rental  (cost=0.00..709.19 rows=218 width=4) (actual time=0.022..0.022 rows=1 loops=1770)"<br
/>"                                    Index Cond: (cf_application_id = $0)"<br />"                                   
Filter:((counter IS NOT NULL) AND ((payment_date IS NULL) OR (payment_date > '2008-01-01'::date)) AND (counter
<>0))"<br /> "                ->  Result  (cost=3.25..3.26 rows=1 width=0) (actual time=0.030..0.031 rows=1
loops=1770)"<br/>"                      InitPlan"<br />"                        ->  Limit  (cost=0.00..3.25 rows=1
width=4)(actual time=0.025..0.026 rows=1 loops=1770)"<br /> "                              ->  Index Scan using
ix_skala_rentalon skala_rental  (cost=0.00..709.19 rows=218 width=4) (actual time=0.022..0.022 rows=1 loops=1770)"<br
/>"                                   Index Cond: (cf_application_id = $0)"<br /> "                                   
Filter:((counter IS NOT NULL) AND ((payment_date IS NULL) OR (payment_date > '2008-01-01'::date)) AND (counter
<>0))"<br />"        SubPlan"<br />"          ->  Aggregate  (cost=1002.55..1002.56 rows=1 width=8) (actual
time=5.594..5.595rows=1 loops=1770)"<br /> "                ->  Index Scan using ix_skala_rental on skala_rental 
(cost=0.00..1002.44rows=43 width=8) (actual time=4.381..5.576 rows=6 loops=1770)"<br />"                      Index
Cond:(cf_application_id = $0)"<br /> "                      Filter: ((due_date <= dateadd('year'::character varying,
2,'2008-01-01 00:00:00'::timestamp without time zone)) AND (due_date >= dateadd('day'::character varying, 1,
dateadd('year'::charactervarying, 1, '2008-01-01 00:00:00'::timestamp without time zone))))"<br /> "          -> 
Aggregate (cost=1002.55..1002.56 rows=1 width=8) (actual time=5.592..5.593 rows=1 loops=1770)"<br />"               
-> Index Scan using ix_skala_rental on skala_rental  (cost=0.00..1002.44 rows=43 width=8) (actual time=4.378..5.574
rows=6loops=1770)"<br /> "                      Index Cond: (cf_application_id = $0)"<br />"                     
Filter:((due_date <= dateadd('year'::character varying, 2, '2008-01-01 00:00:00'::timestamp without time zone)) AND
(due_date>= dateadd('day'::character varying, 1, dateadd('year'::character varying, 1, '2008-01-01
00:00:00'::timestampwithout time zone))))"<br /> "          ->  Aggregate  (cost=808.10..808.11 rows=1 width=8)
(actualtime=1.257..1.258 rows=1 loops=1770)"<br />"                ->  Index Scan using ix_skala_rental on
skala_rental (cost=0.00..807.92 rows=73 width=8) (actual time=0.079..1.229 rows=11 loops=1770)"<br />
"                     Index Cond: (cf_application_id = $0)"<br />"                      Filter: (((payment_date IS
NULL)OR (payment_date > '2008-01-01'::date)) AND (counter <> 0) AND (due_date <= dateadd('year'::character
varying,1, '2008-01-01 00:00:00'::timestamp without time zone)))"<br /> "          ->  Aggregate 
(cost=808.10..808.11rows=1 width=8) (actual time=1.264..1.264 rows=1 loops=1770)"<br />"                ->  Index
Scanusing ix_skala_rental on skala_rental  (cost=0.00..807.92 rows=73 width=8) (actual time=0.080..1.235 rows=11
loops=1770)"<br/> "                      Index Cond: (cf_application_id = $0)"<br />"                      Filter:
(((payment_dateIS NULL) OR (payment_date > '2008-01-01'::date)) AND (counter <> 0) AND (due_date <=
dateadd('year'::charactervarying, 1, '2008-01-01 00:00:00'::timestamp without time zone)))"<br /> "Total runtime:
24476.272ms"<br /><br /><br />