Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TWU45-0002BY-8y for pgsql-performance@postgresql.org; Thu, 08 Nov 2012 15:30:45 +0000 Received: from mail-gg0-f174.google.com ([209.85.161.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TWU43-0005J7-Be for pgsql-performance@postgresql.org; Thu, 08 Nov 2012 15:30:44 +0000 Received: by mail-gg0-f174.google.com with SMTP id k5so585170ggd.19 for ; Thu, 08 Nov 2012 07:30:42 -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=QoykpDxYoxZieGvIr4ORoxMhfftFZHdcGAZ/hVRKNus=; b=VrkkU6jvTLy4Lf7lT5oKXUuzdvoPVZjWQEk/EWyexpLEFapseTkwtzDB2ehDzDcPxG Udl27yNi5L8p5oJMksc5QyGwOozoeD3FXpfsaVsNntjVl7t2bKh+otWJ/1kg+PtJ/c0e qEa2Ik/+8kvDN4jhBwPm8D8xtr0OTWywA2cuoajCwVRX7YlC24CXNffI5YsIwY2OXjRq mDgcofwxlz6uj5fMPeuDWtd121c8jAccefAUNjwkFtcorgjy9Q+C4R+KIfQezEmQK+QJ 7i2m8GB1tp5sNr0k3Qbq2J0cwsYvuTjvmFDQUB7PBGwa9v/CU4Ny+iSberjTSPsrw8Xd lGGQ== Received: by 10.101.132.36 with SMTP id j36mr2435735ann.61.1352388642548; Thu, 08 Nov 2012 07:30:42 -0800 (PST) Received: from [187.36.240.170] ([187.36.240.170]) by mx.google.com with ESMTPS id x10sm540527anl.9.2012.11.08.07.30.41 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 08 Nov 2012 07:30:42 -0800 (PST) Message-ID: <509BD01F.6030602@gmail.com> Date: Thu, 08 Nov 2012 13:30:39 -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: Tom Lane CC: Merlin Moncure , 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> <50995D7C.9020108@gmail.com> <17866.1352229874@sss.pgh.pa.us> <50996583.2080703@gmail.com> <18371.1352231105@sss.pgh.pa.us> <509A42F9.6040403@gmail.com> <10569.1352307514@sss.pgh.pa.us> <509AD4C4.5070003@gmail.com> <6034.1352336282@sss.pgh.pa.us> In-Reply-To: <6034.1352336282@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------030600050906060908050207" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201211/112 X-Sequence-Number: 48478 This is a multi-part message in MIME format. --------------030600050906060908050207 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Em 07-11-2012 22:58, Tom Lane escreveu: > Rodrigo Rosenfeld Rosas writes: >> Ok, I could finally strip part of my database schema that will allow you >> to run the explain query and reproduce the issue. >> There is a simple SQL dump in plain format that you can restore both on >> 9.1 and 9.2 and an example EXPLAIN query so that you can see the >> difference between both versions. >> Please keep me up to date with regards to any progress. Let me know if >> the commit above fixed this issue. > AFAICT, HEAD and 9.2 branch tip plan this query a bit faster than 9.1 > does. Great! What is the estimate for 9.2.2 release? > It does appear that the problem is the same one fixed in that > recent commit: the problem is you've got N join clauses all involving > t.id and so there are lots of redundant ways to use the index on t.id. And what is the reason why fast.sql performs much better than slow.sql? Is it possible to optimize the planner so that both fast.sql and slow.sql finish about the same time? > I've got to say though that this is one of the most bizarre database > schemas I've ever seen. Merlin seems to share your opinion on that. I'd love to try a different database design when I have a chance. What would you guys suggest me for handling my application requirements? The only reason it is bizarre is because I have no idea on how to simplify much our database design using relational databases. And pstore also doesn't sound like a reasonable option either for our requirements. The only other option I can think of is stop splitting transaction_condition in many tables (one for each data type). Then I'd need to include all possible columns in transaction_condition and I'm not sure if it would perform better and what would be the implications with regards to the database size since most columns will be null for each record. This also introduces another issue. I would need to create a trigger to detect if the record is valid upon insertion to avoid creating records with all columns set to NULL for instance. Currently each separate table that store the values have not-null constraints among others to prevent this kind of problem. Triggers are more complicated to maintain, specially because we're used to using an ORM (except for this particular case where I generate the SQL query manually instead of using an ORM for this). Also, we migrate the database using standalone_migrations: https://github.com/thuss/standalone-migrations If we change a single line in the trigger code it won't be easy to see what line has changed in the commit that introduces the change because we would have to create a separate migration to alter the trigger with all code repeated. > It seems to be sort of an unholy combination of > EAV and a star schema. A star schema might not actually be a bad model > for what you're trying to do, but what you want for that is one big fact > table and a collection of *small* detail tables you join to it (small > meaning just one entry per possible value). The way this is set up, you > need to join two or three tables before you can even join to the main > fact table - and those tables don't even have the virtue of being small. > That's never going to perform well. If I understand correctly, you're suggesting that I dropped transaction_condition(id, transaction_id, type_id) and replaced condition_boolean_value(id, condition_id, value) with condition_boolean_value(id, transaction_id, type_id, value) and repeat the same idea for the other tables. Is that right? Would that perform much better? If you think so, I could try this approach when I find some time. But I'd also need to denormalize other related tables I didn't send in the schema dump. For instance, the documents snippets have also a condition_id column. Each field value (transaction_condition) can have multiple contract snippets in a table called condition_document_excerpt(id, document_id, condition_id, "position"). I'd need to remove condition_id from it and append transaction_id and type_id just like the values tables. No big deal if this would speed up our queries. Am I missing something? --------------030600050906060908050207 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Em 07-11-2012 22:58, Tom Lane escreveu: 
Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com> writes:

Ok, I could finally strip part of my database schema that will allow you 
to run the explain query and reproduce the issue.


      

        
There is a simple SQL dump in plain format that you can restore both on 
9.1 and 9.2 and an example EXPLAIN query so that you can see the 
difference between both versions.


      

      

      

        
Please keep me up to date with regards to any progress. Let me know if 
the commit above fixed this issue.


      

      
AFAICT, HEAD and 9.2 branch tip plan this query a bit faster than 9.1
does.

    

    

    Great! What is the estimate for 9.2.2 release?

    

    

      
  It does appear that the problem is the same one fixed in that
recent commit: the problem is you've got N join clauses all involving
t.id and so there are lots of redundant ways to use the index on t.id.

    

    

    And what is the reason why fast.sql performs much better than
    slow.sql? Is it possible to optimize the planner so that both
    fast.sql and slow.sql finish about the same time?

    

    

      
I've got to say though that this is one of the most bizarre database
schemas I've ever seen.

    

    

    Merlin seems to share your opinion on that. I'd love to try a
    different database design when I have a chance.

    

    What would you guys suggest me for handling my application
    requirements?

    

    The only reason it is bizarre is because I have no idea on how to
    simplify much our database design using relational databases. And
    pstore also doesn't sound like a reasonable option either for our
    requirements.

    

    The only other option I can think of is stop splitting
    transaction_condition in many tables (one for each data type). Then
    I'd need to include all possible columns in transaction_condition
    and I'm not sure if it would perform better and what would be the
    implications with regards to the database size since most columns
    will be null for each record. This also introduces another issue. I
    would need to create a trigger to detect if the record is valid upon
    insertion to avoid creating records with all columns set to NULL for
    instance. Currently each separate table that store the values have
    not-null constraints among others to prevent this kind of problem.
    Triggers are more complicated to maintain, specially because we're
    used to using an ORM (except for this particular case where I
    generate the SQL query manually instead of using an ORM for this).

    

    Also, we migrate the database using standalone_migrations:

    

    
    https://github.com/thuss/standalone-migrations

    

    If we change a single line in the trigger code it won't be easy to
    see what line has changed in the commit that introduces the change
    because we would have to create a separate migration to alter the
    trigger with all code repeated.

    

    

      
  It seems to be sort of an unholy combination of
EAV and a star schema.  A star schema might not actually be a bad model
for what you're trying to do, but what you want for that is one big fact
table and a collection of *small* detail tables you join to it (small
meaning just one entry per possible value).  The way this is set up, you
need to join two or three tables before you can even join to the main
fact table - and those tables don't even have the virtue of being small.
That's never going to perform well.

    

    

    If I understand correctly, you're suggesting that I dropped
    transaction_condition(id, transaction_id, type_id) and replaced
    condition_boolean_value(id, condition_id, value) with
    condition_boolean_value(id, transaction_id, type_id, value) and
    repeat the same idea for the other tables.

    

    Is that right? Would that perform much better? If you think so, I
    could try this approach when I find some time. But I'd also need to
    denormalize other related tables I didn't send in the schema dump.
    For instance, the documents snippets have also a condition_id
    column. Each field value (transaction_condition) can have multiple
    contract snippets in a table called condition_document_excerpt(id,
    document_id, condition_id, "position"). I'd need to remove
    condition_id from it and append transaction_id and type_id just like
    the values tables. No big deal if this would speed up our queries.

    

    Am I missing something?

  


--------------030600050906060908050207--