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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql