X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8A0FD11F6AC5 for ; Fri, 31 Mar 2006 21:09:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68540-05 for ; Fri, 31 Mar 2006 21:09:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 7059D11F6665 for ; Fri, 31 Mar 2006 21:09:20 -0400 (AST) Received: from [192.168.0.239] ([207.148.169.40]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Fri, 31 Mar 2006 18:09:18 -0700 In-Reply-To: <20060331155911.GP49405@pervasive.com> References: <2F1712F1-E5E2-455E-A291-06F300A66549@clickspace.com> <20060331155911.GP49405@pervasive.com> Mime-Version: 1.0 (Apple Message framework v749.3) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-3-190317486; protocol="application/pkcs7-signature" Message-Id: Cc: PostgreSQL Performance From: Brendan Duddridge Subject: Re: Query using SeqScan instead of IndexScan Date: Fri, 31 Mar 2006 18:09:18 -0700 To: Jim C. Nasby X-Mailer: Apple Mail (2.749.3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200603/645 X-Sequence-Number: 18082 --Apple-Mail-3-190317486 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Hi Jim, I'm not quite sure what you mean by the correlation of category_id? The category_id is part of a compound primary key in the category_product table. The primary key on category_product is (category_id, product_id). Here's the definitions of the two tables involved in the join: Table "public.category_product" Column | Type | Modifiers ---------------------+----------------------+----------- category_id | integer | not null product_id | integer | not null en_name_sort_order | integer | fr_name_sort_order | integer | merchant_sort_order | integer | price_sort_order | integer | merchant_count | integer | is_active | character varying(5) | Indexes: "x_category_product_pk" PRIMARY KEY, btree (category_id, product_id) "category_product__is_active_idx" btree (is_active) "category_product__merchant_sort_order_idx" btree (merchant_sort_order) "x_category_product__category_id_fk_idx" btree (category_id) CLUSTER "x_category_product__product_id_fk_idx" btree (product_id) Foreign-key constraints: "x_category_product_category_fk" FOREIGN KEY (category_id) REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED "x_category_product_product_fk" FOREIGN KEY (product_id) REFERENCES product(product_id) DEFERRABLE INITIALLY DEFERRED Table "public.product_attribute_value" Column | Type | Modifiers ----------------------------+-----------------------+----------- attribute_id | integer | not null attribute_unit_id | integer | attribute_value_id | integer | boolean_value | character varying(5) | decimal_value | numeric(30,10) | product_attribute_value_id | integer | not null product_id | integer | not null product_reference_id | integer | status_code | character varying(32) | Indexes: "product_attribute_value_pk" PRIMARY KEY, btree (product_attribute_value_id) "product_attribute_value__attribute_id_fk_idx" btree (attribute_id) "product_attribute_value__attribute_unit_id_fk_idx" btree (attribute_unit_id) "product_attribute_value__attribute_value_id_fk_idx" btree (attribute_value_id) "product_attribute_value__product_id_fk_idx" btree (product_id) "product_attribute_value__product_reference_id_fk_idx" btree (product_reference_id) Foreign-key constraints: "product_attribute_value_attribute_fk" FOREIGN KEY (attribute_id) REFERENCES attribute(attribute_id) DEFERRABLE INITIALLY DEFERRED "product_attribute_value_attributeunit_fk" FOREIGN KEY (attribute_unit_id) REFERENCES attribute_unit(attribute_unit_id) DEFERRABLE INITIALLY DEFERRED "product_attribute_value_attributevalue_fk" FOREIGN KEY (attribute_value_id) REFERENCES attribute_value(attribute_value_id) DEFERRABLE INITIALLY DEFERRED "product_attribute_value_product_fk" FOREIGN KEY (product_id) REFERENCES product(product_id) DEFERRABLE INITIALLY DEFERRED "product_attribute_value_productreference_fk" FOREIGN KEY (product_reference_id) REFERENCES product(product_id) DEFERRABLE INITIALLY DEFERRED Not sure if that helps answer your question, but the query is pretty slow. Sometimes it takes 5 - 15 seconds depending on the category_id specified. Thanks, ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Mar 31, 2006, at 8:59 AM, Jim C. Nasby wrote: > What's the correlation of category_id? The current index scan cost > estimator places a heavy penalty on anything with a correlation much > below about 90%. > > On Wed, Mar 29, 2006 at 08:12:28PM -0700, Brendan Duddridge wrote: >> Hi, >> >> I have a query that is using a sequential scan instead of an index >> scan. I've turned off sequential scans and it is in fact faster with >> the index scan. >> >> Here's my before and after. >> >> Before: >> >> ssdev=# SET enable_seqscan TO DEFAULT; >> ssdev=# explain analyze select cp.product_id >> from category_product cp, product_attribute_value pav >> where cp.category_id = 1001082 and cp.product_id = >> pav.product_id; >> >> >> QUERY PLAN >> --------------------------------------------------------------------- >> --- >> --------------------------------------------------------------------- >> --- >> ------------------------------ >> Hash Join (cost=25.52..52140.59 rows=5139 width=4) (actual >> time=4.521..2580.520 rows=19695 loops=1) >> Hash Cond: ("outer".product_id = "inner".product_id) >> -> Seq Scan on product_attribute_value pav (cost=0.00..40127.12 >> rows=2387312 width=4) (actual time=0.039..1469.295 rows=2385846 >> loops=1) >> -> Hash (cost=23.10..23.10 rows=970 width=4) (actual >> time=2.267..2.267 rows=1140 loops=1) >> -> Index Scan using x_category_product__category_id_fk_idx >> on category_product cp (cost=0.00..23.10 rows=970 width=4) (actual >> time=0.122..1.395 rows=1140 loops=1) >> Index Cond: (category_id = 1001082) >> Total runtime: 2584.221 ms >> (7 rows) >> >> >> After: >> >> ssdev=# SET enable_seqscan TO false; >> ssdev=# explain analyze select cp.product_id >> from category_product cp, product_attribute_value pav >> where cp.category_id = 1001082 and cp.product_id = >> pav.product_id; >> >> >> QUERY PLAN >> --------------------------------------------------------------------- >> --- >> --------------------------------------------------------------------- >> --- >> ------------------------------------- >> Nested Loop (cost=0.00..157425.22 rows=5139 width=4) (actual >> time=0.373..71.177 rows=19695 loops=1) >> -> Index Scan using x_category_product__category_id_fk_idx on >> category_product cp (cost=0.00..23.10 rows=970 width=4) (actual >> time=0.129..1.438 rows=1140 loops=1) >> Index Cond: (category_id = 1001082) >> -> Index Scan using product_attribute_value__product_id_fk_idx >> on product_attribute_value pav (cost=0.00..161.51 rows=61 width=4) >> (actual time=0.016..0.053 rows=17 loops=1140) >> Index Cond: ("outer".product_id = pav.product_id) >> Total runtime: 74.747 ms >> (6 rows) >> >> There's quite a big difference in speed there. 2584.221 ms vs. 74.747 >> ms. >> >> Any ideas what I can do to improve this without turning sequential >> scanning off? >> >> Thanks, >> >> ____________________________________________________________________ >> Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com >> >> ClickSpace Interactive Inc. >> Suite L100, 239 - 10th Ave. SE >> Calgary, AB T2G 0V9 >> >> http://www.clickspace.com >> > > > > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly > --Apple-Mail-3-190317486 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGIDCCAtkw ggJCoAMCAQICAw84ujANBgkqhkiG9w0BAQQFADBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0EwHhcNMDUwNzI5MjAxMTQzWhcNMDYwNzI5MjAxMTQzWjBIMR8wHQYDVQQD ExZUaGF3dGUgRnJlZW1haWwgTWVtYmVyMSUwIwYJKoZIhvcNAQkBFhZicmVuZGFuQGNsaWNrc3Bh Y2UuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqWfMCdFgKzXroGNqJuU3eyDC K/Gxts3mtmIdgcHGmUjRKdlBSfkmcCBUDPJIt4QQc91sY3h8Itg0EAsF+1yFECU6afn/1SEAHSDK 6Q86PclF58/Dux2FYNGpeIAw/lisZ2UCyIYoUiWtEDKCBq5jDuYzcGkFS5Csz+/rEL+BHTXQVqnS nUdUaXu9xdZn4wgjB/n65UEYRqr0LGN53CGsFcA9uC+ViS7WyBtzJcP02LMNT+cAZ7TKHw4Q/ZeG 5ptPgQHLTD2wza3GMbPQ4fYK6aNPA+lVzslLSCjyQpg55gVyQQlV0k/5zU5Q5m48ZtqwOqN+fPYd eIB7H5y1SFLd/wIDAQABozMwMTAhBgNVHREEGjAYgRZicmVuZGFuQGNsaWNrc3BhY2UuY29tMAwG A1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAT+6hGvStHoRO0OCHlJev31BpUlvPSpbYVKJN i5kCMF164sSX7j0IRvcyU6DfUuW7samTluXJbANyuX1ZIpXulGen5EEc2NV7eU/rMz6ExklMShTh ++azcvmMjOzTuqXi3ZsIjwzBBhqCB0U++kYlMlHEYla2qKLFbXY6PiH0a8swggM/MIICqKADAgEC AgENMA0GCSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBl MRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQL Ex9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29u YWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5j b20wHhcNMDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UE ChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFs IEZyZWVtYWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftO ucqZWh5owHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9x VsuvPAsH5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAe ZBlyYLf7AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJo dHRwOi8vY3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8E BAMCAQYwKQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3 DQEBBQUAA4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9 reFhYsPZOhl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAa WzVNd+NWIXiC3CEZNd4ksdMdRv9dX2VPMYIC5zCCAuMCAQEwaTBiMQswCQYDVQQGEwJaQTElMCMG A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNv bmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujAJBgUrDgMCGgUAoIIBUzAYBgkqhkiG9w0BCQMx CwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA0MDEwMTA5MThaMCMGCSqGSIb3DQEJBDEW BBSGLrSHEp9LWDZMoFTpDUNpH9QV2zB4BgkrBgEEAYI3EAQxazBpMGIxCzAJBgNVBAYTAlpBMSUw IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIDDzi6MHoGCyqGSIb3DQEJEAILMWugaTBiMQswCQYD VQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMj VGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujANBgkqhkiG9w0BAQEFAASC AQAyx6QTWD42FWIp5SNZyXiiBZ7RoJlY1gMf1Rq1xEc3rmn8L6Eu6dMjyvLZA8tpwexbA2jqL7Q4 tsfTEupMT3VE/k+AAGW3D5Idt/T2geNKqjk7S33aiQgSO2lkW27r881tNeU95RAfYKRXaZHRQygC TVDedzAHUaYpqeuGCfP3NUykKAaS0XhHeJWDR5ZVUuVzVLHTrE7apD+yTR32mUFTo0caW0vyv+Tu 2yy71xloRuTi7zPFgG/5kGn0j7z9Uh9ojTRrVqKoKORbagvas6dscObehDovnvq9QospRamk7GJs tCaL+u2cM/OkhHhfOxa8D5007hgy0tn1/PwnbEYeAAAAAAAA --Apple-Mail-3-190317486--