Re: Performance across multiple schemas - Mailing list 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