Skip site navigation (1) Skip section navigation (2)

Performance across multiple schemas

From: Robins <tharakan(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance across multiple schemas
Date: 2007-08-28 13:05:24
Message-ID: 36af4bed0708280605j5448ac52of6f7dc08791f0ab2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I have just reorganized a relatively decent sized query such that its
constituent functions / tables are now spread over 3-4 schemas.

However, the query has for some reason now become very slow (earlier used to
take about 20 seconds, now takes about 500 seconds). The explain analyse
(given below) doesn't help either.

(Of what I did try, reducing the number of functions made the query faster,
which frankly doesn't help me at all. Sadly removing the functions
one-by-one led me to two of them which were taking a lot of time (the 3rd
last and the 4th last) but their reason is to me still unknown. Besides,
even after removing these two fields the query is still painfully slow as
compared to its previous performance).

All functions are STABLE (but that shouldnt matter because this analyse was
specifically done for 1 row).
Most functions are in the 'processing' schema and most tables are in the
fundsys1 schema.
Almost all the required fields are indexed (It was working fast enough
earlier, so I dont think that should be an issue).
Did a VACUUM ANALYSE before running this query.
The NULL with COALESCE is just a temporary hack to replace a variable with
NULL to run this query for a small set.

Could someone confirm as to whether a query across multiple schemas is known
to have any kind of a degraded performance ?
Any other ideas ?

======================================
"Nested Loop  (cost=206.15..246.63 rows=37 width=16) (actual time=
362.139..296937.587 rows=841 loops=1)"
"  ->  Merge Join  (cost=206.15..206.33 rows=1 width=12) (actual time=
12.817..12.832 rows=1 loops=1)"
"        Merge Cond: (main.scheme_code = jn_set_schemecode.scheme_code)"
"        ->  Sort  (cost=201.24..201.31 rows=27 width=12) (actual time=
12.672..12.683 rows=8 loops=1)"
"              Sort Key: main.variant_scheme_code"
"              ->  Seq Scan on main  (cost=0.00..200.60 rows=27 width=12)
(actual time=0.029..6.728 rows=2593 loops=1)"
"                    Filter: (variant_scheme_code = scheme_code)"
"        ->  Sort  (cost=4.91..4.93 rows=9 width=4) (actual time=
0.107..0.110 rows=1 loops=1)"
"              Sort Key: jn_set_schemecode.scheme_code"
"              ->  Seq Scan on jn_set_schemecode  (cost=0.00..4.76 rows=9
width=4) (actual time=0.074..0.076 rows=1 loops=1)"
"                    Filter: (set_id = 10)"
"  ->  Seq Scan on "month"  (cost=0.00..25.41 rows=841 width=4) (actual
time=0.033..3.049 rows=841 loops=1)"
"Total runtime: 296939.886 ms"

======================================
SELECT
        main.scheme_code,
            (
            (processing.fund_month_end_mean(main.scheme_code,
'2005-1-1'::date, '2007-6-30'::date)*12) -
            (processing.risk_free_index_month_end_mean('2005-1-1'::date,
'2007-6-30'::date) * 12)
            )/(processing.fund_month_end_stddev_pop(main.scheme_code,
'2005-1-1'::date, '2007-6-30'::date)*sqrt(12)),

            processing.fund_month_end_stddev_pop(main.scheme_code,
'2005-1-1'::date, '2007-6-30'::date) ,

                (
                    (processing.covariance_fund_index_monthly(
main.scheme_code, COALESCE(NULL, stated_index), '2005-1-1'::date,
'2007-6-30'::date)*12)/
                        (processing.fund_month_end_stddev_pop(
main.scheme_code, '2005-1-1'::date, '2007-6-30'::date)*sqrt(12) *
                        processing.index_month_end_stddev_pop(COALESCE(NULL,
stated_index), '2005-1-1'::date, '2007-6-30'::date)*sqrt(12))
                ),

            processing.information_ratio_monthly(main.scheme_code,
COALESCE(NULL, stated_index), '2005-1-1'::date, '2007-6-30'::date) ,
            (
                (processing.fund_month_end_mean(main.scheme_code,
'2005-1-1'::date, '2007-6-30'::date)*12) -
                ((processing.risk_free_index_month_end_mean('2005-1-1'::date,
'2007-6-30'::date) * 12) +
                    ((
                        (processing.covariance_fund_index_monthly(
main.scheme_code, COALESCE(NULL, stated_index), '2005-1-1'::date,
'2007-6-30'::date)*12) /
                        (processing.index_month_end_variance(COALESCE(NULL,
stated_index), '2005-1-1'::date, '2007-6-30'::date)* 12)
                    )*
                    (
                        (processing.index_month_end_mean(COALESCE(NULL,
stated_index), '2005-1-1'::date, '2007-6-30'::date)*12) -

(processing.risk_free_index_month_end_mean('2005-1-1'::date,
'2007-6-30'::date) * 12)
                    )
                    )
                ),
                (
                    (processing.covariance_fund_index_monthly(
main.scheme_code, COALESCE(NULL, stated_index), '2005-1-1'::date,
'2007-6-30'::date)*12) /
                    (processing.index_month_end_variance(COALESCE(NULL,
stated_index), '2005-1-1'::date, '2007-6-30'::date)* 12)
                ),
            processing.upside_capture_ratio_monthly(main.scheme_code,
COALESCE(NULL, stated_index), '2005-1-1'::date, '2007-6-30'::date) ,
            processing.downside_capture_ratio_monthly(main.scheme_code,
COALESCE(NULL, stated_index), '2005-1-1'::date, '2007-6-30'::date) ,
            processing.fund_return(main.scheme_code, '2007-6-30'::date, '1
year', true) ,
            processing.fund_return(main.scheme_code, '2007-6-30'::date, '2
years', true) ,
            processing.fund_return(main.scheme_code, '2007-6-30'::date, '3
years', true) ,
            processing.fund_return(main.scheme_code, '2007-6-30'::date, '5
years', true) ,
            processing.rolling_return(main.scheme_code, '2007-6-30'::date,
'1 year', '1 month', '1 day') ,
            processing.calendar_year_return(main.scheme_code, (extract(year
from now()))::integer) ,
            processing.calendar_year_return(main.scheme_code, (extract(year
from now()) - 1)::integer),
            processing.calendar_year_return(main.scheme_code, (extract(year
from now()) - 2)::integer),
            processing.days_to_liquidate(main.scheme_code,
'2007-6-30'::date) as days_to_liquidate,
            processing.deviation_from_index(main.scheme_code, COALESCE(NULL,
stated_index), '2007-6-30'::date) ,
            (SELECT index_full_name FROM fundsys1.fs_indices INNER JOIN
fundsys1.main ON main.stated_index = index_code where main.scheme_code =
jn_set_schemecode.scheme_code),
            (SELECT stated_index FROM fundsys1.main where main.scheme_code =
jn_set_schemecode.scheme_code),
            processing.number_of_companies_in_index(jn_set_schemecode.scheme_code,
lookup_tables.month.month_end_date),
            processing.percentage_of_assets_in_stocks_as_in_benchmark(jn_set_schemecode.scheme_code,
lookup_tables.month.month_end_date)
        FROM lookup_tables.month, fundsys1.main
            INNER JOIN output.jn_set_schemecode ON
jn_set_schemecode.scheme_code = main.scheme_code
        WHERE jn_set_schemecode.set_id=10
            AND main.variant_scheme_code = main.scheme_code
        ORDER BY main.scheme_code
======================================

Thanks
Robins Tharakan

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2007-08-28 13:12:06
Subject: Re: server performance issues - suggestions fortuning
Previous:From: Heikki LinnakangasDate: 2007-08-28 10:20:16
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group