Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVoKg-0007gZ-LW for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 18:57:06 +0000 Received: from mail-gh0-f174.google.com ([209.85.160.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVoKd-0007ak-Hs for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 18:57:05 +0000 Received: by mail-gh0-f174.google.com with SMTP id g15so165425ghb.19 for ; Tue, 06 Nov 2012 10:57:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type; bh=gC4q6qW5jSBf5lbwrE5SN18AECrdULpa5I73GX16Ar4=; b=M/k4Ow1yIxCh/HNEvO2spyq1nWAcnjBNr3Iezi0zAerAngLoRIxcFl/8oG1yHP7fBW lbl/3Sas96DYCQJ5jNTybMZ71vFwzjf4Im+eK+LdcQThIKRUjJK0ywkvmtIkVJoiPZ7l to7OrsgF8pgmdI8+KaJNOS4XMs36dcPRMRD5pYwzs+PelLSR3cxTlzxZ7rFRzhyDtc// ydQ06Qk6pvs9CebWPA2HkPT4gP8HeCEjWFGBciSI07YuCVr21kOfMcfWEPvi+2Pdon1w E55fG815GfIDNVoTBSRAN219w9tLW8SqHGmQLv1hMySgCMD0fZNEOfYR3aRsQR/p/PQ3 Daeg== Received: by 10.236.181.169 with SMTP id l29mr2079331yhm.44.1352228222630; Tue, 06 Nov 2012 10:57:02 -0800 (PST) Received: from [187.36.244.195] ([187.36.244.195]) by mx.google.com with ESMTPS id u15sm19525397anq.14.2012.11.06.10.57.01 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 06 Nov 2012 10:57:01 -0800 (PST) Message-ID: <50995D7C.9020108@gmail.com> Date: Tue, 06 Nov 2012 16:57:00 -0200 From: Rodrigo Rosenfeld Rosas User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.10) Gecko/20121027 Icedove/10.0.10 MIME-Version: 1.0 To: Merlin Moncure CC: pgsql-performance@postgresql.org Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2 References: <509944DE.6020400@gmail.com> <5099525B.5070909@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010305030707060403000003" X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201211/66 X-Sequence-Number: 48432 This is a multi-part message in MIME format. --------------010305030707060403000003 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Em 06-11-2012 16:42, Merlin Moncure escreveu: > On Tue, Nov 6, 2012 at 12:09 PM, Rodrigo Rosenfeld Rosas > wrote: >> http://explain.depesz.com/s/ToX (fast on 9.1) >> http://explain.depesz.com/s/65t (fast on 9.2) >> http://explain.depesz.com/s/gZm (slow on 9.1) >> http://explain.depesz.com/s/END (slow on 9.2 - funny that the generated URL >> was END while this was my last explain :D ) > Hm -- looking at your 'slow' 9.2 query, it is reporting that the query > took 3 seconds (reported times are in milliseconds). How are you > timing the data? What happens when you run explain analyze > from psql (as in, how long does it take)? The time I reported in the tables of my first message were the time reported by pgAdmin3 (compiled from source). But I get similar time when I run like this: time psql -p 5432 -f slow.sql db_name > slow-9.2-again.explain real 1m56.353s user 0m0.068s sys 0m0.020s slow-9.2-again.explain: http://explain.depesz.com/s/zF1 >> Let me explain how the application works, how the database was designed and >> hopefully you'll be able to guide me in the correct way to design the >> database for this use case. >> >> Our application will present a big contract to some attorneys. There is >> currently a dynamic template with around 800 fields to be extracted from >> each contract in our system. These fields can be of different types >> (boolean, string, number, currency, percents, fixed options, dates, >> time-spans and so on). There is a fields tree that is maintained by the >> application editors. The application will allow the attorneys to read the >> contracts and highlight parts of the contract where they extracted each >> field from and associate each field with its value interpreted by the >> attorney and store the reference to what paragraphs in the contract >> demonstrate where the value came from. >> >> Then there is an interface that will allow clients to search for >> transactions based on its associated contracts and those ~800 fields. For >> the particular query above, 14 of the 800 fields have been searched by this >> particular user (most of them were boolean ones plus a few options and a >> string field). Usually the queries perform much better when less than 10 >> fields are used in the criteria. But our client wants us to handle up to 20 >> fields in a single query or they won't close the deal and this is a really >> important client to us. >> >> So, for the time being my only plan is to rollback to PG 9.1 and replace my >> query builder that currently generate queries like slow.sql and change it to >> generate the queries like fast.sql but I'm pretty sure this approach should >> be avoided. I just don't know any other alternative for the time being. >> >> What database design would you recommend me for this use case? > I would strongly consider investigation of hstore type along with > gist/gin index. > select * from company_transaction where contract_attributes @> > 'State=>Delaware, Paid=Y'; > etc I'm not very familiar with hstore yet but this was one of the reasons I wanted to migrate to PG 9.2 but I won't be able to migrate the application quickly to use hstore. Also, I'm not sure if hstore allows us to be as flexible as we currently are (c1 and (c2 or c3 and not (c4 and c5))). c == condition > Barring that, I would then consider complete elimination of integer > proxies for your variables. They make your query virtually impossible > to read/write, and they don't help. I'm not sure if I understood what you're talking about. The template is dynamic and contains lots of information for each field, like type (number, percent, string, date, etc), parent_id (auto-referencing), aggregator_id (also auto-referencing) and several other columns. But the values associate the field id (type_id) and the transaction id in a unique way (see unique index in my first message of the thread). Then I need different tables to store the actual value because we're using SQL instead of MongoDB or something else. The table that stores the value depend on the field type. Maybe it would help me to understand if you could provide some example for the design you're proposing. Thank you very much, Rodrigo. --------------010305030707060403000003 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Em 06-11-2012 16:42, Merlin Moncure escreveu: 
On Tue, Nov 6, 2012 at 12:09 PM, Rodrigo Rosenfeld Rosas
<rr.rosas@gmail.com> wrote:

http://explain.depesz.com/s/ToX (fast on 9.1)
http://explain.depesz.com/s/65t (fast on 9.2)
http://explain.depesz.com/s/gZm (slow on 9.1)
http://explain.depesz.com/s/END (slow on 9.2 - funny that the generated URL
was END while this was my last explain :D )

Hm -- looking at your 'slow' 9.2 query, it is reporting that the query
took 3 seconds  (reported times are in milliseconds).  How are you
timing the data?  What happens when you run explain analyze
<your_query> from psql (as in, how long does it take)?

The time I reported in the tables of my first message were the time reported by pgAdmin3 (compiled from source).

But I get similar time when I run like this:

time psql -p 5432 -f slow.sql db_name > slow-9.2-again.explain

real    1m56.353s
user    0m0.068s
sys     0m0.020s

slow-9.2-again.explain: http://explain.depesz.com/s/zF1


      

        
Let me explain how the application works, how the database was designed and
hopefully you'll be able to guide me in the correct way to design the
database for this use case.

Our application will present a big contract to some attorneys. There is
currently a dynamic template with around 800 fields to be extracted from
each contract in our system. These fields can be of different types
(boolean, string, number, currency, percents, fixed options, dates,
time-spans and so on). There is a fields tree that is maintained by the
application editors. The application will allow the attorneys to read the
contracts and highlight parts of the contract where they extracted each
field from and associate each field with its value interpreted by the
attorney and store the reference to what paragraphs in the contract
demonstrate where the value came from.

Then there is an interface that will allow clients to search for
transactions based on its associated contracts and those ~800 fields. For
the particular query above, 14 of the 800 fields have been searched by this
particular user (most of them were boolean ones plus a few options and a
string field). Usually the queries perform much better when less than 10
fields are used in the criteria. But our client wants us to handle up to 20
fields in a single query or they won't close the deal and this is a really
important client to us.

So, for the time being my only plan is to rollback to PG 9.1 and replace my
query builder that currently generate queries like slow.sql and change it to
generate the queries like fast.sql but I'm pretty sure this approach should
be avoided. I just don't know any other alternative for the time being.

What database design would you recommend me for this use case?


      

      
I would strongly consider investigation of hstore type along with
gist/gin index.
select * from company_transaction where contract_attributes @>
'State=>Delaware, Paid=Y';
etc

    

    

    I'm not very familiar with hstore yet but this was one of the
    reasons I wanted to migrate to PG 9.2 but I won't be able to migrate
    the application quickly to use hstore.

    

    Also, I'm not sure if hstore allows us to be as flexible as we
    currently are (c1 and (c2 or c3 and not (c4 and c5))). c ==
    condition

    

    

      
Barring that, I would then consider complete elimination of integer
proxies for your variables.  They make your query virtually impossible
to read/write, and they don't help.

    

    

    I'm not sure if I understood what you're talking about. The template
    is dynamic and contains lots of information for each field, like
    type (number, percent, string, date, etc), parent_id
    (auto-referencing), aggregator_id (also auto-referencing) and
    several other columns. But the values associate the field id
    (type_id) and the transaction id in a unique way (see unique index
    in my first message of the thread). Then I need different tables to
    store the actual value because we're using SQL instead of MongoDB or
    something else. The table that stores the value depend on the field
    type.

    

    Maybe it would help me to understand if you could provide some
    example for the design you're proposing.

    

    Thank you very much,

    Rodrigo.

    

  


--------------010305030707060403000003--