Re: help-simplify query - Mailing list pgsql-sql

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.