Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVmh3-0003Jd-Ka for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 17:12:05 +0000 Received: from mail-gh0-f174.google.com ([209.85.160.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVmh0-0005s4-TW for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 17:12:05 +0000 Received: by mail-gh0-f174.google.com with SMTP id g15so132140ghb.19 for ; Tue, 06 Nov 2012 09:12: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:subject :content-type; bh=87PF8/70R6vU38PZkRFTyI7H64PiugPu3DCjVihIsqI=; b=gitvXQ+faw0aeuBau0Uc/FIdeVm3or5Bs/Jz89vv83cvyer4p4a4np88aPGRyQU5Hv 9YZUiuNHKr8720ec7hEAx52AATDn6q9WFDW1T04eiFGWkBFTZeCFQMxOwVeWaml2UmWP sUdt2rKHgDttfjbww6YX+EUfoj+26SLS2BJppbJAtltZzxFfYePNGhWStF0eoJj9sXmW reBnQnkq8O2f55afGY/OPQBd50tdnl1ej87uxTx7tM7R+HGOX+cm4iW63Wnbn2NJ8k5e NMvO6oQwJ7ROq5D95W40gNMzNWjOv+ae20TZDrHmMqdq/d8307xVQpUzCAn8N1Irq4MZ y01g== Received: by 10.101.128.30 with SMTP id f30mr491967ann.17.1352221921917; Tue, 06 Nov 2012 09:12:01 -0800 (PST) Received: from [187.36.244.195] ([187.36.244.195]) by mx.google.com with ESMTPS id t14sm11131368anl.17.2012.11.06.09.12.00 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 06 Nov 2012 09:12:01 -0800 (PST) Message-ID: <509944DE.6020400@gmail.com> Date: Tue, 06 Nov 2012 15:11:58 -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: pgsql-performance@postgresql.org Subject: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2 Content-Type: multipart/alternative; boundary="------------020709020702090502040508" X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201211/57 X-Sequence-Number: 48423 This is a multi-part message in MIME format. --------------020709020702090502040508 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello, this is my first message to this list, so sorry if this is not the right place to discuss this or if some data is missing from this message. I'll gladly send any data you request that would help us to understand this issue. I don't believe I'm allowed to share the actual database dump, but other than that I can provide much more details you might ask for. I can't understand why PG 9.2 performs so differently from PG 9.1. I tested these queries in my Debian unstable amd64 box after restoring the same database dump this morning in both PG 9.1 (Debian unstable repository) and PG9.2 (Debian experimental repository) with same settings: https://gist.github.com/3f1f3aad3847155e1e35 Ignore all lines like the line below because it doesn't make any difference on my tests if I just remove them or any other column from the SELECT clause: " exists(select id from condition_document_excerpt where condition_id=c1686.id) as v1686_has_reference," The results below are pretty much the same if you assume "SELECT 1 FROM ...". I have proper indices created for all tables and the query is fast in both PG versions when I don't use many conditions in the WHERE clause. fast.sql returns the same data as slow.sql but it returns much faster in my tests with PG 9.1. So here are the completion times for each query on each PG version: Query | PG 9.1 | PG 9.2 | ----------------------------------- fast.sql| 650 ms (0.65s) | 690s | slow.sql| 419s | 111s | For the curious, the results would be very similar to slow.sql if I use inner joins with the conditions inside the WHERE moved to the "ON" clause of the inner join instead of the left outer join + global WHERE approach. But I don't have this option anyway because this query is generated dynamically and not all my queries are "ALL"-like queries. Here are the relevant indices (id is SERIAL primary key in all tables): CREATE UNIQUE INDEX transaction_condition_transaction_id_type_id_idx ON transaction_condition USING btree (transaction_id, type_id); CREATE INDEX index_transaction_condition_on_transaction_id ON transaction_condition USING btree (transaction_id); CREATE INDEX index_transaction_condition_on_type_id ON transaction_condition USING btree (type_id); CREATE INDEX acquirer_target_names ON company_transaction USING btree (acquiror_company_name COLLATE pg_catalog."default", target_company_name COLLATE pg_catalog."default"); CREATE INDEX index_company_transaction_on_target_company_name ON company_transaction USING btree (target_company_name COLLATE pg_catalog."default"); CREATE INDEX index_company_transaction_on_date ON company_transaction USING btree (date); CREATE INDEX index_company_transaction_on_edit_status ON company_transaction USING btree (edit_status COLLATE pg_catalog."default"); CREATE UNIQUE INDEX index_condition_boolean_value_on_condition_id ON condition_boolean_value USING btree (condition_id); CREATE INDEX index_condition_boolean_value_on_value_and_condition_id ON condition_boolean_value USING btree (value COLLATE pg_catalog."default", condition_id); CREATE UNIQUE INDEX index_condition_option_value_on_condition_id ON condition_option_value USING btree (condition_id); CREATE INDEX index_condition_option_value_on_value_id_and_condition_id ON condition_option_value USING btree (value_id, condition_id); CREATE INDEX index_condition_option_label_on_type_id_and_position ON condition_option_label USING btree (type_id, "position"); CREATE INDEX index_condition_option_label_on_type_id_and_value ON condition_option_label USING btree (type_id, value COLLATE pg_catalog."default"); CREATE UNIQUE INDEX index_condition_string_value_on_condition_id ON condition_string_value USING btree (condition_id); CREATE INDEX index_condition_string_value_on_value_and_condition_id ON condition_string_value USING btree (value COLLATE pg_catalog."default", condition_id); Please let me know of any suggestions on how to try to get similar results in PG 9.2 as well as to understand why fast.sql performs so much better than slow.sql on PG 9.1. Best, Rodrigo. --------------020709020702090502040508 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hello, this is my first message to this list, so sorry if this is not the right place to discuss this or if some data is missing from this message.

I'll gladly send any data you request that would help us to understand this issue. I don't believe I'm allowed to share the actual database dump, but other than that I can provide much more details you might ask for.

I can't understand why PG 9.2 performs so differently from PG 9.1.

I tested these queries in my Debian unstable amd64 box after restoring the same database dump this morning in both PG 9.1 (Debian unstable repository) and PG9.2 (Debian experimental repository) with same settings:

https://gist.github.com/3f1f3aad3847155e1e35

Ignore all lines like the line below because it doesn't make any difference on my tests if I just remove them or any other column from the SELECT clause:

"  exists(select id from condition_document_excerpt where condition_id=c1686.id) as v1686_has_reference,"

The results below are pretty much the same if you assume "SELECT 1 FROM ...".

I have proper indices created for all tables and the query  is fast in both PG versions when I don't use many conditions in the WHERE clause.

fast.sql returns the same data as slow.sql but it returns much faster in my tests with PG 9.1.

So here are the completion times for each query on each PG version:

Query   | PG 9.1         | PG 9.2 |
-----------------------------------
fast.sql| 650 ms (0.65s) | 690s   |
slow.sql| 419s           | 111s   |


For the curious, the results would be very similar to slow.sql if I use inner joins with the conditions inside the WHERE moved to the "ON" clause of the inner join instead of the left outer join + global WHERE approach. But I don't have this option anyway because this query is generated dynamically and not all my queries are "ALL"-like queries.

Here are the relevant indices (id is SERIAL primary key in all tables):

CREATE UNIQUE INDEX transaction_condition_transaction_id_type_id_idx
  ON transaction_condition
  USING btree
  (transaction_id, type_id);
CREATE INDEX index_transaction_condition_on_transaction_id
  ON transaction_condition
  USING btree
  (transaction_id);
CREATE INDEX index_transaction_condition_on_type_id
  ON transaction_condition
  USING btree
  (type_id);

CREATE INDEX acquirer_target_names
  ON company_transaction
  USING btree
  (acquiror_company_name COLLATE pg_catalog."default", target_company_name COLLATE pg_catalog."default");
CREATE INDEX index_company_transaction_on_target_company_name
  ON company_transaction
  USING btree
  (target_company_name COLLATE pg_catalog."default");
CREATE INDEX index_company_transaction_on_date
  ON company_transaction
  USING btree
  (date);
CREATE INDEX index_company_transaction_on_edit_status
  ON company_transaction
  USING btree
  (edit_status COLLATE pg_catalog."default");

CREATE UNIQUE INDEX index_condition_boolean_value_on_condition_id
  ON condition_boolean_value
  USING btree
  (condition_id);
CREATE INDEX index_condition_boolean_value_on_value_and_condition_id
  ON condition_boolean_value
  USING btree
  (value COLLATE pg_catalog."default", condition_id);

CREATE UNIQUE INDEX index_condition_option_value_on_condition_id
  ON condition_option_value
  USING btree
  (condition_id);
CREATE INDEX index_condition_option_value_on_value_id_and_condition_id
  ON condition_option_value
  USING btree
  (value_id, condition_id);


CREATE INDEX index_condition_option_label_on_type_id_and_position
  ON condition_option_label
  USING btree
  (type_id, "position");
CREATE INDEX index_condition_option_label_on_type_id_and_value
  ON condition_option_label
  USING btree
  (type_id, value COLLATE pg_catalog."default");


CREATE UNIQUE INDEX index_condition_string_value_on_condition_id
  ON condition_string_value
  USING btree
  (condition_id);
CREATE INDEX index_condition_string_value_on_value_and_condition_id
  ON condition_string_value
  USING btree
  (value COLLATE pg_catalog."default", condition_id);


Please let me know of any suggestions on how to try to get similar results in PG 9.2 as well as to understand why fast.sql performs so much better than slow.sql on PG 9.1.

Best,
Rodrigo.
--------------020709020702090502040508--