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 6E7D19DC9D6 for ; Fri, 24 Feb 2006 02:54:49 -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 60460-07 for ; Fri, 24 Feb 2006 02:54:48 -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 44B899DC89B for ; Fri, 24 Feb 2006 02:54:46 -0400 (AST) Received: from [10.0.1.3] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Thu, 23 Feb 2006 23:54:44 -0700 Mime-Version: 1.0 (Apple Message framework v746.2) To: PostgreSQL Performance Message-Id: <1831440F-F848-471D-ACE9-F84CE9F4E359@clickspace.com> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-2--751870699; protocol="application/pkcs7-signature" Cc: Tony Copping Subject: Really really slow query. What's a better way? From: Brendan Duddridge Date: Thu, 23 Feb 2006 23:54:45 -0700 X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.107 required=5 tests=[AWL=0.106, HTML_MESSAGE=0.001] X-Spam-Score: 0.107 X-Spam-Level: X-Archive-Number: 200602/379 X-Sequence-Number: 17365 --Apple-Mail-2--751870699 Content-Type: multipart/alternative; boundary=Apple-Mail-1--751871314 --Apple-Mail-1--751871314 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Hi, We're executing a query that has the following plan and we're wondering given the size of the data set, what's a better way to write the query? It's been running since 2pm 2 days ago. explain DELETE FROM cds.cds_mspecxx WHERE ProdID not in (SELECT stage.ProdID FROM cds_stage.cds_Catalog stage where stage.countryCode = 'us') and countryCode = 'us'; QUERY PLAN ------------------------------------------------------------------------ --------------------------- Index Scan using pk_mspecxx on cds_mspecxx (cost=53360.87..208989078645.48 rows=7377879 width=6) Index Cond: ((countrycode)::text = 'us'::text) Filter: (NOT (subplan)) SubPlan -> Materialize (cost=53360.87..77607.54 rows=1629167 width=12) -> Seq Scan on cds_catalog stage (cost=0.00..43776.70 rows=1629167 width=12) Filter: ((countrycode)::text = 'us'::text) (7 rows) 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 --Apple-Mail-1--751871314 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 Hi,

We're executing a query = that has the following plan and we're wondering given the size of the = data set, what's a better way to write the query? It's been running = since 2pm 2 days ago.

explain DELETE FROM = cds.cds_mspecxx WHERE ProdID not in (SELECT stage.ProdID FROM = cds_stage.cds_Catalog stage where stage.countryCode =3D 'us') and = countryCode =3D 'us';

QUERY PLAN=A0

-------------------------------------------------------= --------------------------------------------

Index Scan using pk_mspecxx on cds_mspecxx = (cost=3D53360.87..208989078645.48 rows=3D7377879 = width=3D6)

Index Cond: ((countrycode)::text =3D = 'us'::text)

Filter: (NOT = (subplan))

SubPlan

-> Materialize = (cost=3D53360.87..77607.54 rows=3D1629167 width=3D12)

-> Seq Scan on cds_catalog stage = (cost=3D0.00..43776.70 rows=3D1629167 width=3D12)

Filter: ((countrycode)::text =3D = 'us'::text)

(7 rows)

Thanks,

_________________________________________________________= ___________
Brendan Duddridge=A0| CTO | 403-277-5591 x24 | =A0brendan@clickspace.com =

ClickSpace = Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB = =A0T2G 0V9

http://www.clickspace.com=A0

= --Apple-Mail-1--751871314-- --Apple-Mail-2--751870699 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 CwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjAyMjQwNjU0NDZaMCMGCSqGSIb3DQEJBDEW BBQr/4VMmsWuPP5+fhMQMmi87CMl5DB4BgkrBgEEAYI3EAQxazBpMGIxCzAJBgNVBAYTAlpBMSUw IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIDDzi6MHoGCyqGSIb3DQEJEAILMWugaTBiMQswCQYD VQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMj VGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujANBgkqhkiG9w0BAQEFAASC AQBA5/j829ffqaCbrhBFqg3+ELdC1U3M0d3SdNf8ry6q4eXeJ8caE9ioCS7T4hf1826VlL/0JmTb aMXDC+Hyr/TwSgJ4qLJojWHMtYfJhxETl5LBumFe+UjTwk3WtpVOlletq5fN6T1RdmQqYF7/x+ed GCDvWpiZS7WNUkp3egeH7o28a4h6duY7ynYbyi02vno0rzrLMYn4wuCfTN+59t9f7XlHKCGGJvUH xlmui3pkvPxp/gVj2At9fCP/3tLTSFCwqN5Uokjc+2UB79gqJ+zRagA3sUyKNdr234cc1LtxkNyV 4Gnt8B1EsH6B9RzfldhNTttMQoQ6CrCQFVRJxsVrAAAAAAAA --Apple-Mail-2--751870699--