pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Rowell <richard@bowmansystems.com>
To: pgsql-performance@postgresql.org
Subject: Improve performance of query
Date: Thu, 16 Dec 2004 10:11:07 -0600
Message-ID: <1103213467.9908.65.camel@richard> (raw)

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
536870912
richard:/usr/share/cups/model# cat /proc/sys/kernel/shmall
536870912
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
FROM
(
    SELECT a.uid AS answer_id, a.client_id, a.question_id, recordset_id,
date_effective
    FROM da_answer a
    WHERE a.date_effective <= 9999999999.0
    AND a.inactive != 1
    AND
    (
            5000 = 4000 
            OR
            (EXISTS (SELECT * FROM svp_getparentproviderids(1) WHERE
svp_getparentproviderids = a.provider_id))
    )
    UNION
    SELECT a.uid AS answer_id, a.client_id, a.question_id, recordset_id,
date_effective
    FROM da_answer a,
    (    
        SELECT main_id 
        FROM da_data_restrict
        WHERE type_id = 2        
        AND (provider_id IN (SELECT * FROM svp_getparentproviderids(1)))
               
        UNION
            
            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
            UNION
            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
svp_getparentproviderids(1)))))
                AND sa.covered_by_roi = 1
            UNION
            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
da_data_restrict(provider_id);
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
da_data_restrict(client_id);
create index in_da_dr_type_provider on
da_data_restrict(type_id,provider_id);

create index in_da_data_rec_provider_id ON
da_data_restrict_except_closed(provider_id);
create index in_da_data_rec_type_id ON
da_data_restrict_except_closed(type_id);
create index in_da_data_rec_main_id ON
da_data_restrict_except_closed(main_id);
create index in_da_data_rec_except_provider_id ON
da_data_restrict_except_closed(except_provider_id);

create index in_da_data_reo_provider_id ON
da_data_restrict_except_open(provider_id);
create index in_da_data_reo_type_id ON
da_data_restrict_except_open(type_id);
create index in_da_data_reo_main_id ON
da_data_restrict_except_open(main_id);
create index in_da_data_reo_except_provider_id ON
da_data_restrict_except_open(except_provider_id);

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
da_answer(restricted_open_except);
create index in_da_answer_restricted_closed_except ON
da_answer(restricted_closed_except);
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,
covered_by_roi);

create index in_da_question_mal ON da_question(min_access_level);

Attachments:

explain.txt (text/plain, 8.6K) download 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, date_effective -> 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)) 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) Filter: (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 = "inner".uid) -> 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 loops=1) Hash 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 loops=1) -> 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 loops=1) 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 loops=1) -> 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 loops=1) Index Cond: (restricted_open_except = 1) Filter: ((restricted = 0) AND (covered_by_roi = 1) AND (NOT (subplan))) 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 loops=1841) 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 loops=1) 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 NULL)) Total runtime: 4986.508 ms (58 rows) count ------- 21117 (1 row)

view thread (6+ messages)  latest in thread

Message-ID: <1103213467.9908.65.camel@richard>
Permalink:  ../1103213467.9908.65.camel@richard/
Also on:    postgresql.org/message-id/1103213467.9908.65.camel@richard

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: richard@bowmansystems.com
  Subject: Re: Improve performance of query
  In-Reply-To: <1103213467.9908.65.camel@richard>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox