Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TlPR1-0006TL-DV for pgsql-performance@arkaria.postgresql.org; Wed, 19 Dec 2012 19:36:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TlPR0-0006eE-JK for pgsql-performance@arkaria.postgresql.org; Wed, 19 Dec 2012 19:36:06 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TlPQz-0006e8-2n for pgsql-performance@postgresql.org; Wed, 19 Dec 2012 19:36:05 +0000 Received: from mail-qc0-f171.google.com ([209.85.216.171]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TlPQw-0002xI-BR for pgsql-performance@postgresql.org; Wed, 19 Dec 2012 19:36:03 +0000 Received: by mail-qc0-f171.google.com with SMTP id d1so1346156qca.30 for ; Wed, 19 Dec 2012 11:36:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:message-id:date:from:user-agent:mime-version:to:subject :content-type; bh=wxc5TvKY8kJP9aZswvsZRT2eGmigSg1XLKok5dkVhjU=; b=vG5r05pTbHt1mADZ6GJxA+7ViLTqYSA02J/5UKQP6zf80JJRAL/xw5h7mG18i65jyL H6XOW4FyvqIaZ/5wuhMUaJqd+2pFTPsXzyTgH6ydccFCuh55K3AbNcn7spf5TipvaHAy o9PbbBtvqC4yC/dC3XR4mCuO2e+L3e8XDQ9oX9X3K8iaXrgiEP9pngxgt0P4vJZ3uQ/4 +d2zuLQ23B3ayR3j/S8nHwfMVw9BtqvCBZWsPEDOSOaT4mAtknNrbLyjcTCUzR2Za95W 8OlBGVrdGlHI79mEMOonqa+KfLyiQEPMw9S4BYaN8BPbF9bi6B2P48hcL/H1XfD0pHva Pfew== X-Received: by 10.224.196.137 with SMTP id eg9mr1530732qab.99.1355945761459; Wed, 19 Dec 2012 11:36:01 -0800 (PST) Received: from [187.64.149.121] ([187.64.149.121]) by mx.google.com with ESMTPS id j20sm1265143qaq.17.2012.12.19.11.35.59 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 19 Dec 2012 11:36:00 -0800 (PST) Message-ID: <50D2171D.9010608@gmail.com> Date: Wed, 19 Dec 2012 17:35:57 -0200 From: Rodrigo Rosenfeld Rosas User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.11) Gecko/20121122 Icedove/10.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: PG 9.1 performance loss due to query plan being changed depending on db data (4s vs 200ms) Content-Type: multipart/alternative; boundary="------------080408070406080509000201" X-Pg-Spam-Score: -1.5 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------080408070406080509000201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Trying again since I couldn't post without being subscribed. The message got stalled and was never sent, so I just subscribed and I'm trying again. Original message I tried to send two days ago: ---------------------------------- I've explained a bit of how my application works in this thread as well as the reasons why I couldn't use PG 9.2.1 due to performance issues and had to rollback to 9.1. http://postgresql.1045698.n5.nabble.com/Query-completed-in-lt-1s-in-PG-9-1-and-700s-in-PG-9-2-td5730899.html Now I found that 9.2.2 was released while 9.1 is performing worse for the same db schema, but the data is now different. So, here are the output of some explain analyze: Old DB dump, PG 9.1: http://explain.depesz.com/s/mvf (0.2s) New DB dump, PG 9.1: http://explain.depesz.com/s/vT2k (4.3s) New DB dump, PG 9.2.2: http://explain.depesz.com/s/uu0 (0.04s) I've already upgraded back to PG 9.2.2 but I thought you might be interested on backporting that improvement to 9.1 as well and I'm not even sure if the bug above was fixed intentionally or by chance so I'd like to be sure about that... The query I used was: SELECT t.id as tid, t.acquiror_company_name || ' / ' || t.target_company_name as tname, exists(select id from condition_document_excerpt where condition_id=c1726.id) as v1726_has_reference, l1726.value as v1726 FROM company_transaction t left outer join condition_option_value v1726 inner join transaction_condition c1726 on c1726.id=v1726.condition_id and type_id=1726 inner join condition_option_label l1726 on l1726.id=v1726.value_id on c1726.transaction_id = t.id WHERE t.edit_status = 'Finished' and (t.id in (select transaction_id from condition_option_value v1726 inner join transaction_condition c1726 on c1726.id=v1726.condition_id and type_id=1726 inner join condition_option_label l1726 on l1726.id=v1726.value_id AND (v1726.value_id = 278) ) ) ORDER BY t.acquiror_company_name, t.target_company_name If I simplify the WHERE condition it performs much better in 9.1 for this particular case (but I can't do that as the queries are generated dynamically, please see first mentioned link to understand the reason): WHERE t.edit_status = 'Finished' and v1726.value_id = 278 New DB dump, 9.1, simplified query: http://explain.depesz.com/s/oj1 (0.03s) The inner query (for the "in" clause) alone takes 44ms: select transaction_id from condition_option_value v1726 inner join transaction_condition c1726 on c1726.id=v1726.condition_id and type_id=1726 inner join condition_option_label l1726 on l1726.id=v1726.value_id AND (v1726.value_id = 278) So, what would be the reason for the full original query to take over 4s in PG 9.1? Best, Rodrigo. --------------080408070406080509000201 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Trying again since I couldn't post without being subscribed. The message got stalled and was never sent, so I just subscribed and I'm trying again. Original message I tried to send two days ago:

----------------------------------

I've explained a bit of how my application works in this thread as well as the reasons why I couldn't use PG 9.2.1 due to performance issues and had to rollback to 9.1.

http://postgresql.1045698.n5.nabble.com/Query-completed-in-lt-1s-in-PG-9-1-and-700s-in-PG-9-2-td5730899.html

Now I found that 9.2.2 was released while 9.1 is performing worse for the same db schema, but the data is now different.

So, here are the output of some explain analyze:

Old DB dump, PG 9.1: http://explain.depesz.com/s/mvf (0.2s)
New DB dump, PG 9.1: http://explain.depesz.com/s/vT2k (4.3s)
New DB dump, PG 9.2.2: http://explain.depesz.com/s/uu0 (0.04s)

I've already upgraded back to PG 9.2.2 but I thought you might be interested on backporting that improvement to 9.1 as well and I'm not even sure if the bug above was fixed intentionally or by chance so I'd like to be sure about that...

The query I used was:

SELECT t.id as tid,
  t.acquiror_company_name || ' / ' || t.target_company_name as tname,
  exists(select id from condition_document_excerpt where condition_id=c1726.id) as v1726_has_reference,
  l1726.value as v1726
 FROM company_transaction t
 left outer join
condition_option_value v1726
 inner join transaction_condition c1726
  on c1726.id=v1726.condition_id and type_id=1726
  inner join condition_option_label l1726
   on l1726.id=v1726.value_id
 on c1726.transaction_id = t.id
 WHERE t.edit_status = 'Finished' and
 (t.id in (select transaction_id from
condition_option_value v1726
 inner join transaction_condition c1726
  on c1726.id=v1726.condition_id and type_id=1726
  inner join condition_option_label l1726
   on l1726.id=v1726.value_id
 AND (v1726.value_id = 278)
)
)
 ORDER BY
 t.acquiror_company_name, t.target_company_name



If I simplify the WHERE condition it performs much better in 9.1 for this particular case (but I can't do that as the queries are generated dynamically, please see first mentioned link to understand the reason):

 WHERE t.edit_status = 'Finished' and v1726.value_id = 278

New DB dump, 9.1, simplified query: http://explain.depesz.com/s/oj1 (0.03s)

The inner query (for the "in" clause) alone takes 44ms:

select transaction_id from
condition_option_value v1726
 inner join transaction_condition c1726
  on c1726.id=v1726.condition_id and type_id=1726
  inner join condition_option_label l1726
   on l1726.id=v1726.value_id
 AND (v1726.value_id = 278)


So, what would be the reason for the full original query to take over 4s in PG 9.1?

Best,

Rodrigo.

--------------080408070406080509000201--