Thread: help-simplify query
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 />
dateadd() is not sql postgresql standard, it's mssql
in postgresql i only need to use +/- operators and interval for the same
function
dateadd('year', 1, '2008/1/1') ==> date '2008/1/1' + interval '1 year'
rafizeldi wrote:
>
> 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"
>
>
--
View this message in context: http://old.nabble.com/help-simplify-query-tp25943795p26277339.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.