I'm trying to port our application from MS-SQL to Postgres.  We have
implemented all of our rather complicated application security in the
database.  The query that follows takes a half of a second or less on
MS-SQL server and around 5 seconds on Postgres.  My concern is that this
data set is rather "small" by our applications standards.  It is not
unusual for the da_answer table to have 2-4 million records.  I'm
worried that if this very small data set is taking 5 seconds, then a
"regular sized" data set will take far too long.

I originally thought the NOT EXISTS on the
"da_data_restrict_except_open" table was killing performance, but the
query took the exact same amount of time after I deleted all rows from
this table.  Note that the hard-coded 999999999.0, and 4000 parameters,
as well as the parameter to svp_getparentproviders are the three
variables that change from one run of this query to the next.

I'm using Postgres 7.4.5 as packaged in Debian.  shared_buffers is set
to 57344 and sort_mem=4096.

The machine has an AMD 1.8+ and ` gig of RAM.  Here are some relevant
performance statistics:
richard:/usr/share/cups/model# cat /proc/sys/kernel/shmmax
richard:/usr/share/cups/model# cat /proc/sys/kernel/shmall
richard:/home/richard# hdparm -tT /dev/hda
 Timing cached reads:   1112 MB in  2.00 seconds = 556.00 MB/sec
 Timing buffered disk reads:  176 MB in  3.02 seconds =  58.28 MB/sec

I have included an EXPLAIN ANALYZE, relevant table counts, and relevant
indexing information.  If anyone has any suggestions on how to improve
performance....  TIA!

SELECT tab.answer_id, client_id, question_id, recordset_id,
date_effective, virt_field_name
    SELECT a.uid AS answer_id, a.client_id, a.question_id, recordset_id,
    FROM da_answer a
    WHERE a.date_effective <= 9999999999.0
    AND a.inactive != 1
            5000 = 4000 
            (EXISTS (SELECT * FROM svp_getparentproviderids(1) WHERE
svp_getparentproviderids = a.provider_id))
    SELECT a.uid AS answer_id, a.client_id, a.question_id, recordset_id,
    FROM da_answer a,
        SELECT main_id 
        FROM da_data_restrict
        WHERE type_id = 2        
        AND (provider_id IN (SELECT * FROM svp_getparentproviderids(1)))
            SELECT sa.uid AS main_id 
            FROM da_answer sa
                JOIN da_data_restrict_except_closed dr ON dr.main_id =
sa.uid AND dr.type_id = 2 AND dr.except_provider_id = 1
            WHERE (restricted = 1) 
                AND (restricted_closed_except = 1)                
                AND sa.covered_by_roi = 1
            SELECT sa.uid AS main_id 
            FROM da_answer sa
            WHERE (restricted = 0) 
                AND (restricted_open_except = 1)                
                AND (NOT EXISTS (SELECT dr.main_id FROM
da_data_restrict_except_open dr WHERE (dr.main_id = sa.uid) AND
(dr.type_id = 2) AND (dr.except_provider_id in (select * from
                AND sa.covered_by_roi = 1
            SELECT sa.uid AS main_id FROM da_answer sa WHERE (restricted
= 0) AND (restricted_open_except = 0)
                AND sa.covered_by_roi = 1
    ) sec
    WHERE a.covered_by_roi = 1
        AND a.date_effective <= 9999999999.0
        AND a.inactive != 1
        AND a.uid = sec.main_id
        AND 5000 > 4000
) tab, da_question q
WHERE tab.question_id = q.uid AND (min_access_level <= 4000 OR
min_access_level IS NULL)

Table counts from relevant tables
da_question                      1095
da_answer                       21117
da_question                      1095
da_data_restrict_except_closed   3087
da_data_restrict_except_open    13391
svp_getparentproviderids(1)         1

Relevant Index
create index in_da_data_restrict_provider_id on
create index in_da_data_restrict_main_id on da_data_restrict(main_id);
create index in_da_data_restrict_type_id on da_data_restrict(type_id);
create index in_da_data_restrict_client_id on
create index in_da_dr_type_provider on

create index in_da_data_rec_provider_id ON
create index in_da_data_rec_type_id ON
create index in_da_data_rec_main_id ON
create index in_da_data_rec_except_provider_id ON

create index in_da_data_reo_provider_id ON
create index in_da_data_reo_type_id ON
create index in_da_data_reo_main_id ON
create index in_da_data_reo_except_provider_id ON

create index in_da_answer_client_id ON da_answer(client_id);
create index in_da_answer_provider_id ON da_answer(provider_id);
create index in_da_answer_question_id ON da_answer(question_id);
create index in_da_answer_recordset_id ON da_answer(recordset_id);
create index in_da_answer_restricted ON da_answer(restricted);
create index in_da_answer_restricted_open_except ON
create index in_da_answer_restricted_closed_except ON
create index in_da_answer_date_effective ON da_answer(date_effective);
create index in_da_answer_inactive ON da_answer(inactive);
create index in_da_answer_covered_by_roi ON da_answer(covered_by_roi);

create index in_da_ed_inactive_roi ON da_answer(date_effective,inactive,

create index in_da_question_mal ON da_question(min_access_level);

                                     QUERY PLAN                                 
 Hash Join  (cost=1054186.23..1054631.36 rows=4496 width=67) (actual 
time=4902.250..4979.060 rows=7653 loops=1)
   Hash Cond: ("outer".question_id = "inner".uid)
   ->  Subquery Scan tab  (cost=1054123.62..1054457.09 rows=13339 width=24) 
(actual time=4896.963..4933.896 rows=7657 loops=1)
         ->  Unique  (cost=1054123.62..1054323.70 rows=13339 width=24) (actual 
time=4896.948..4915.498 rows=7657 loops=1)
               ->  Sort  (cost=1054123.62..1054156.96 rows=13339 width=24) 
(actual time=4896.944..4903.402 rows=7717 loops=1)
                     Sort Key: answer_id, client_id, question_id, recordset_id, 
                     ->  Append  (cost=0.00..1053209.67 rows=13339 width=24) 
(actual time=279.091..4841.605 rows=7717 loops=1)
                           ->  Subquery Scan "*SELECT* 1"  (cost=0.00..64034.15 
rows=10540 width=24) (actual time=279.089..4419.371 rows=161 loops=1)
                                 ->  Seq Scan on da_answer a  
(cost=0.00..63928.75 rows=10540 width=24) (actual time=279.080..4418.808 
rows=161 loops=1)
                                       Filter: ((date_effective <= 
9999999999::double precision) AND (inactive <> 1) AND (subplan))
                                         ->  Function Scan on 
svp_getparentproviderids  (cost=0.00..15.00 rows=5 width=4) (actual 
time=0.203..0.203 rows=0 loops=21089)
(svp_getparentproviderids = $1)
                           ->  Subquery Scan "*SELECT* 2"  
(cost=988627.58..989175.52 rows=2799 width=24) (actual time=290.730..417.720 
rows=7556 loops=1)
                                 ->  Hash Join  (cost=988627.58..989147.53 
rows=2799 width=24) (actual time=290.722..395.739 rows=7556 loops=1)
                                       Hash Cond: ("outer".main_id = 
                                       ->  Subquery Scan sec  
(cost=987913.23..988002.59 rows=5957 width=4) (actual time=203.862..225.462 
rows=7567 loops=1)
                                             ->  Unique  
(cost=987913.23..987943.02 rows=5957 width=4) (actual time=203.851..215.834 
rows=7567 loops=1)
                                                   ->  Sort  
(cost=987913.23..987928.12 rows=5957 width=4) (actual time=203.843..207.273 
rows=7567 loops=1)
                                                         Sort Key: main_id
                                                         ->  Append  
(cost=160.61..987539.72 rows=5957 width=4) (actual time=35.798..187.293 
rows=7567 loops=1)
                                                               ->  Subquery 
Scan "*SELECT* 1"  (cost=160.61..164.53 rows=46 width=4) (actual 
time=35.796..35.923 rows=35 loops=1)
                                                                     ->  Hash 
Join  (cost=160.61..164.07 rows=46 width=4) (actual time=35.791..35.868 rows=35 
Cond: ("outer".svp_getparentproviderids = "inner".provider_id)
HashAggregate  (cost=15.00..15.00 rows=200 width=4) (actual time=0.491..0.492 
rows=1 loops=1)
 ->  Function Scan on svp_getparentproviderids  (cost=0.00..12.50 rows=1000 
width=4) (actual time=0.473..0.474 rows=1 loops=1)
Hash  (cost=145.50..145.50 rows=46 width=8) (actual time=35.256..35.256 rows=0 
 ->  Index Scan using in_da_dr_type_provider on da_data_restrict  
(cost=0.00..145.50 rows=46 width=8) (actual time=0.041..26.627 rows=7280 
       Index Cond: (type_id = 2)
                                                               ->  Subquery 
Scan "*SELECT* 2"  (cost=0.00..53.69 rows=1 width=4) (actual time=0.030..0.030 
rows=0 loops=1)
                                                                     ->  Nested 
Loop  (cost=0.00..53.68 rows=1 width=4) (actual time=0.028..0.028 rows=0 
Index Scan using in_da_data_rec_except_provider_id on 
da_data_restrict_except_closed dr  (cost=0.00..50.65 rows=1 width=4) (actual 
time=0.026..0.026 rows=0 loops=1)
 Index Cond: (except_provider_id = 1)
 Filter: (type_id = 2)
Index Scan using da_answer_pkey on da_answer sa  (cost=0.00..3.02 rows=1 
width=4) (never executed)
 Index Cond: ("outer".main_id = sa.uid)
 Filter: ((restricted = 1) AND (restricted_closed_except = 1) AND 
(covered_by_roi = 1))
                                                               ->  Subquery 
Scan "*SELECT* 3"  (cost=0.00..986638.62 rows=678 width=4) (actual 
time=0.346..77.393 rows=1841 loops=1)
                                                                     ->  Index 
Scan using in_da_answer_restricted_open_except on da_answer sa  
(cost=0.00..986631.84 rows=678 width=4) (actual time=0.342..74.614 rows=1841 
Index Cond: (restricted_open_except = 1)
Filter: ((restricted = 0) AND (covered_by_roi = 1) AND (NOT (subplan)))
 Nested Loop IN Join  (cost=0.00..227.09 rows=1 width=4) (actual 
time=0.026..0.026 rows=0 loops=1841)
   Join Filter: ("outer".except_provider_id = "inner".svp_getparentproviderids)
   ->  Index Scan using in_da_data_reo_main_id on da_data_restrict_except_open 
dr  (cost=0.00..212.09 rows=1 width=8) (actual time=0.009..0.014 rows=2 
         Index Cond: (main_id = $0)
         Filter: (type_id = 2)
   ->  Function Scan on svp_getparentproviderids  (cost=0.00..12.50 rows=1000 
width=4) (actual time=0.002..0.003 rows=1 loops=3793)
                                                               ->  Subquery 
Scan "*SELECT* 4"  (cost=0.00..682.87 rows=5232 width=4) (actual 
time=0.064..69.107 rows=5691 loops=1)
                                                                     ->  Seq 
Scan on da_answer sa  (cost=0.00..630.55 rows=5232 width=4) (actual 
time=0.059..61.052 rows=5691 loops=1)
Filter: ((restricted = 0) AND (restricted_open_except = 0) AND (covered_by_roi 
= 1))
                                       ->  Hash  (cost=630.55..630.55 rows=9922 
width=24) (actual time=86.699..86.699 rows=0 loops=1)
                                             ->  Seq Scan on da_answer a  
(cost=0.00..630.55 rows=9922 width=24) (actual time=0.043..73.232 rows=10062 
                                                   Filter: ((covered_by_roi = 
1) AND (date_effective <= 9999999999::double precision) AND (inactive <> 1))
   ->  Hash  (cost=61.69..61.69 rows=369 width=47) (actual time=5.241..5.241 
rows=0 loops=1)
         ->  Seq Scan on da_question q  (cost=0.00..61.69 rows=369 width=47) 
(actual time=0.026..4.071 rows=1087 loops=1)
               Filter: ((min_access_level <= 4000) OR (min_access_level IS 
 Total runtime: 4986.508 ms
(58 rows)

(1 row)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to