Dear All
I need to simplify this query, It takes a lot of time to execute
Since "skala_rental" table has 2.3million rows(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
I guest the problem is on this subquery
sr.counter =(SELECT MIN(counter)
FROM skala_rental
WHERE cf_application_id = cf.cf_application_id
AND (payment_date IS NULL OR payment_date > '2008/1/1') AND counter <>
0)
This part is really slow in execution
and there are redundant "WHERE clause"/Filters between subqueries
I cannot provide my server configuarion, i have no access there
Is there any idea/suggestion to make this better query?
Please help me to find the better one
---------->This is my query that need to simplfy
SELECT
--principle_1 2009
COALESCE ((SELECT SUM(principle)
FROM skala_rental
WHERE cf_application_id = cf.cf_application_id
AND (payment_date IS NULL OR payment_date > '2008/1/1')
AND counter <> 0
AND due_date <= DATEADD('year',1,'2008/1/1')), 0) AS principle_1,
--interest_1 2009
COALESCE ((SELECT SUM(interest)
FROM skala_rental
WHERE cf_application_id = cf.cf_application_id
AND (payment_date IS NULL OR payment_date > '2008/1/1')
AND counter <> 0
AND due_date <= DATEADD('year',1,'2008/1/1')), 0) AS interest_1,
--principle_2 2010
COALESCE ((SELECT SUM(principle)
FROM skala_rental
WHERE cf_application_id = cf.cf_application_id
AND due_date BETWEEN DATEADD('day',1,DATEADD('year',1,'2008/1/1'))
AND DATEADD('year',2,'2008/1/1')),0) AS principle_2,
--interest_2 2010
COALESCE ((SELECT SUM(interest)
FROM skala_rental
WHERE cf_application_id = cf.cf_application_id
AND due_date BETWEEN DATEADD('day',1,DATEADD('year',1,'2008/1/1'))
AND DATEADD('year',2,'2008/1/1')),0) AS interest_2
FROM
cf_application cf
JOIN skala_rental sr ON sr.cf_application_id = cf.cf_application_id
WHERE 1=1
AND cf.drawdown_date <= '2008/1/1'
AND (cf.terminate_date IS NULL OR cf.terminate_date > '2008/1/1')
AND sr.counter =
(SELECT MIN(counter)
FROM skala_rental
WHERE cf_application_id = cf.cf_application_id
AND (payment_date IS NULL OR payment_date > '2008/1/1')
AND counter <> 0)
AND cf.branch_id = 2
ORDER BY cf.cf_drawdown_number
----------> This is skala_rental & cf_application schema,
some unecessary fields were removed
CREATE TABLE skala_rental
( skala_rental_id bigint NOT NULL, counter integer NOT NULL,
due_date date NOT NULL, payment_date date,
rental double precision NOT NULL, principle double precision NOT NULL,
interest double precision NOT NULL, cf_application_id bigint,
CONSTRAINT skala_rental_pkey PRIMARY KEY (skala_rental_id)
) WITH (OIDS=FALSE);
CREATE INDEX ix_skala_rental ON skala_rental
USING btree (cf_application_id, counter, payment_date, due_date);
CREATE TABLE cf_application
( cf_application_id bigint NOT NULL, cf_application_number character
varying(32),
old_cf_application_number character varying(32), organization_id bigint,
branch_id bigint NOT NULL, drawdown_date date,
terminate_date date, last_due_date date,
CONSTRAINT cf_application_pkey PRIMARY KEY (cf_application_id),
CONSTRAINT fk9889f3744997923b FOREIGN KEY (record_status_id)
REFERENCES status (status_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
) WITH (OIDS=FALSE);
CREATE INDEX ix_cf_application ON cf_application
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);
----------> The Explain Analyze:
"Sort (cost=1185871.07..1185871.81 rows=297 width=29) (actual
time=24472.933..24474.260 rows=1770 loops=1)"
" Sort Key: cf.cf_drawdown_number"
" Sort Method: quicksort Memory: 268kB"
" -> Nested Loop (cost=3.26..1185858.87 rows=297 width=29) (actual
time=33.743..24460.729 rows=1770 loops=1)"
" -> 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)"
" Filter: ((drawdown_date <= '2008-01-01'::date) AND
((terminate_date IS NULL) OR (terminate_date > '2008-01-01'::date)) AND
(branch_id = 2))"
" -> Index Scan using ix_skala_rental on skala_rental sr
(cost=3.26..18.79 rows=6 width=12) (actual time=0.008..0.009 rows=1
loops=1770)"
" Index Cond: ((sr.cf_application_id = cf.cf_application_id)
AND (sr.counter = (subplan)))"
" SubPlan"
" -> Result (cost=3.25..3.26 rows=1 width=0) (actual
time=0.030..0.031 rows=1 loops=1770)"
" InitPlan"
" -> Limit (cost=0.00..3.25 rows=1 width=4) (actual
time=0.025..0.026 rows=1 loops=1770)"
" -> Index Scan using ix_skala_rental on
skala_rental (cost=0.00..709.19 rows=218 width=4) (actual time=0.022..0.022
rows=1 loops=1770)"
" Index Cond: (cf_application_id = $0)"
" Filter: ((counter IS NOT NULL) AND
((payment_date IS NULL) OR (payment_date > '2008-01-01'::date)) AND (counter
<> 0))"
" -> Result (cost=3.25..3.26 rows=1 width=0) (actual
time=0.030..0.031 rows=1 loops=1770)"
" InitPlan"
" -> Limit (cost=0.00..3.25 rows=1 width=4) (actual
time=0.025..0.026 rows=1 loops=1770)"
" -> Index Scan using ix_skala_rental on
skala_rental (cost=0.00..709.19 rows=218 width=4) (actual time=0.022..0.022
rows=1 loops=1770)"
" Index Cond: (cf_application_id = $0)"
" Filter: ((counter IS NOT NULL) AND
((payment_date IS NULL) OR (payment_date > '2008-01-01'::date)) AND (counter
<> 0))"
" SubPlan"
" -> Aggregate (cost=1002.55..1002.56 rows=1 width=8) (actual
time=5.594..5.595 rows=1 loops=1770)"
" -> Index Scan using ix_skala_rental on skala_rental
(cost=0.00..1002.44 rows=43 width=8) (actual time=4.381..5.576 rows=6
loops=1770)"
" Index Cond: (cf_application_id = $0)"
" 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'::timestamp without time zone))))"
" -> Aggregate (cost=1002.55..1002.56 rows=1 width=8) (actual
time=5.592..5.593 rows=1 loops=1770)"
" -> 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=6
loops=1770)"
" Index Cond: (cf_application_id = $0)"
" 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'::timestamp without time zone))))"
" -> Aggregate (cost=808.10..808.11 rows=1 width=8) (actual
time=1.257..1.258 rows=1 loops=1770)"
" -> 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)"
" Index Cond: (cf_application_id = $0)"
" 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)))"
" -> Aggregate (cost=808.10..808.11 rows=1 width=8) (actual
time=1.264..1.264 rows=1 loops=1770)"
" -> Index Scan using 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)"
" Index Cond: (cf_application_id = $0)"
" 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)))"
"Total runtime: 24476.272 ms"
