Re: help-simplify query

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.