Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 086F72E00D4 for ; Thu, 17 Apr 2008 15:31:51 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 32462-02-3 for ; Thu, 17 Apr 2008 15:31:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.246]) by developer.postgresql.org (Postfix) with ESMTP id C6DA72E00B6 for ; Thu, 17 Apr 2008 15:31:15 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so45526ana.33 for ; Thu, 17 Apr 2008 11:31:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type; bh=1MQgtUGv44qAtjaK94AIjllT7ou2VB6VrLIKcoesDPY=; b=aK8vjB67qPUNFfNPV9e8FPAGWS0rwhmUErxOPOwQFWM3bAjWcMaCCHDYKqXWVkpVmrgwzUpjT/6gFAZGRp3+9kf6fYT+WZTv2+CIvVVAApG2RIX+CVr9BRYNhlinqa4xxC5KgWM3XNAVfQpK4mp9XXGOJu3x25VIHlxx8KdOD5c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type; b=V6doWTM7AxZf5oDeBAuJTCj77yaw3fo7H3+Z+8vy+mL4KyupsfgAtbnPWmGW9AsZiQ/54O3ZWvpk2xCpM/fSyVB1CMukEuADHDgZL6nFVmL1QDstLHHhZWD4BgpVxHf5HfDbDMpKvguCx3UeOu23INeDU/+9upZCC18bcEFvWrE= Received: by 10.100.190.14 with SMTP id n14mr3067881anf.142.1208457074938; Thu, 17 Apr 2008 11:31:14 -0700 (PDT) Received: from ?190.17.40.188? ( [190.17.40.188]) by mx.google.com with ESMTPS id c38sm20619620anc.31.2008.04.17.11.31.12 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 17 Apr 2008 11:31:13 -0700 (PDT) Message-ID: <4807976E.3070707@gmail.com> Date: Thu, 17 Apr 2008 15:31:10 -0300 From: Rodrigo Gonzalez User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: kevin kempter CC: pgsql-performance@postgresql.org Subject: Re: seq scan issue... References: In-Reply-To: Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms080306040807070507000306" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/251 X-Sequence-Number: 29744 This is a cryptographically signed message in MIME format. --------------ms080306040807070507000306 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable kevin kempter escribi=F3: > Hi List; > > I have a large tble (playback_device) with 6million rows in it. The=20 > aff_id_tmp1 table has 600,000 rows. > > I also have this query: > select distinct > tmp1.affiliate_id, > tmp1.name, > tmp1.description, > tmp1.create_dt, > tmp1.playback_device_id, > pf.segment_id > from > aff_id_tmp1 tmp1, > playback_fragment pf > where > tmp1.playback_device_id =3D pf.playback_device_id ; > > > The Primary Key for playback_device is the playback_device_id > there is also an index on playback_device_id on the aff_id_tmp1 table. > The only join condition I have is on this key pair (I've posted my=20 > explain plan below) > > > - why am I still getting a seq scan ? > > Thanks in advance. > > > > > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > Explain PLan > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > explain > select distinct > tmp1.affiliate_id, > tmp1.name, > tmp1.description, > tmp1.create_dt, > tmp1.playback_device_id, > pf.segment_id > from > aff_id_tmp1 tmp1, > playback_fragment pf > where > tmp1.playback_device_id =3D pf.playback_device_id ; > > > Unique (cost=3D2966361.56..3194555.91 rows=3D10104496 width=3D97) > -> Sort (cost=3D2966361.56..2998960.76 rows=3D13039677 width=3D97)= > Sort Key: tmp1.affiliate_id, tmp1.name, tmp1.description,=20 > tmp1.create_dt, > tmp1.playback_device_id, pf.segment_id > -> Hash Join (cost=3D23925.45..814071.14 rows=3D13039677 wid= th=3D97) > Hash Cond: (pf.playback_device_id =3D=20 > tmp1.playback_device_id) > -> Seq Scan on playback_fragment pf =20 > (cost=3D0.00..464153.77 rows=3D130 > 39677 width=3D16) > -> Hash (cost=3D16031.31..16031.31 rows=3D631531 width= =3D89) > -> Seq Scan on aff_id_tmp1 tmp1 =20 > (cost=3D0.00..16031.31 rows=3D63 > 1531 width=3D89) > (1068 rows) > > Cause you are getting all the rows so pgsql need to scan all the table...= --------------ms080306040807070507000306 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIJCzCC AuAwggJJoAMCAQICEFKu3/n0oTKNim1sXE5AIgswDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UE BhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA3MDkwNTEyMzIyNFoX DTA4MDkwNDEyMzIyNFowRTEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEiMCAG CSqGSIb3DQEJARYTcmpnb256YWxlQGdtYWlsLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEP ADCCAQoCggEBALjktG7EMWR5pPz82DfSue+kNXszsWfpEWrRtFt0jBM01Pz6tiYVTy1IMgxl R03UOuGdOkyqK9TkLxCbN80I0xBbM/1r4Z35FJ/EN+Vm/DPBGOwuxJEw4Dj8/5uDrPkRVC0b j+YRxZsM4TOJjIrrqO/6xPRe4pns+gYvHdcW98mR2hyeTIBnx7WcQfPUMOzmPUt/nJNakxgH A1B2724RMeGQpMh8pHUJ7B04RNt+f3sLzr9c+a815AhdNqPPMjmcnkRtjcdCK3K+dngtQzp4 TbiUnUBQtjCvF31EK75NcbRJdGZhJCRjsjJtW5Nep9TJ7tBrTHUxNEDt11cDJnLe4i8CAwEA AaMwMC4wHgYDVR0RBBcwFYETcmpnb256YWxlQGdtYWlsLmNvbTAMBgNVHRMBAf8EAjAAMA0G CSqGSIb3DQEBBQUAA4GBAKX5592Z5SwMOtNc99oEJGEPwk8K2/z/0G8VSkJoWR1rYxWvBF7+ biHZ4T7sQBUw0FUgyWV+swc/DtTDZDr+J2WWKIcMFMRHX+Fmo7SyqPWL1+jhmSolbWoq6eHj U2YOxPDJflcl8okL5b85y4m9lsCHS0qtIZ1KJ5oFod1zFOxlMIIC4DCCAkmgAwIBAgIQUq7f +fShMo2KbWxcTkAiCzANBgkqhkiG9w0BAQUFADBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMc VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFs IEZyZWVtYWlsIElzc3VpbmcgQ0EwHhcNMDcwOTA1MTIzMjI0WhcNMDgwOTA0MTIzMjI0WjBF MR8wHQYDVQQDExZUaGF3dGUgRnJlZW1haWwgTWVtYmVyMSIwIAYJKoZIhvcNAQkBFhNyamdv bnphbGVAZ21haWwuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAuOS0bsQx ZHmk/PzYN9K576Q1ezOxZ+kRatG0W3SMEzTU/Pq2JhVPLUgyDGVHTdQ64Z06TKor1OQvEJs3 zQjTEFsz/WvhnfkUn8Q35Wb8M8EY7C7EkTDgOPz/m4Os+RFULRuP5hHFmwzhM4mMiuuo7/rE 9F7imez6Bi8d1xb3yZHaHJ5MgGfHtZxB89Qw7OY9S3+ck1qTGAcDUHbvbhEx4ZCkyHykdQns HThE235/ewvOv1z5rzXkCF02o88yOZyeRG2Nx0Ircr52eC1DOnhNuJSdQFC2MK8XfUQrvk1x tEl0ZmEkJGOyMm1bk16n1Mnu0GtMdTE0QO3XVwMmct7iLwIDAQABozAwLjAeBgNVHREEFzAV gRNyamdvbnphbGVAZ21haWwuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEFBQADgYEA pfnn3ZnlLAw601z32gQkYQ/CTwrb/P/QbxVKQmhZHWtjFa8EXv5uIdnhPuxAFTDQVSDJZX6z Bz8O1MNkOv4nZZYohwwUxEdf4WajtLKo9YvX6OGZKiVtairp4eNTZg7E8Ml+VyXyiQvlvznL ib2WwIdLSq0hnUonmgWh3XMU7GUwggM/MIICqKADAgECAgENMA0GCSqGSIb3DQEBBQUAMIHR MQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRv d24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9u IFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwg Q0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZy ZWVtYWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftO ucqZWh5owHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Va qj9xVsuvPAsH5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e2 0TxhBEAeZBlyYLf7AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6 MDigNqA0hjJodHRwOi8vY3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENB LmNybDALBgNVHQ8EBAMCAQYwKQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJl bDItMTM4MA0GCSqGSIb3DQEBBQUAA4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0wh uPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmO jCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC3CEZNd4ksdMdRv9dX2VPMYIDZDCCA2AC AQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkg THRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEFKu 3/n0oTKNim1sXE5AIgswCQYFKw4DAhoFAKCCAcMwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDgwNDE3MTgzMTEwWjAjBgkqhkiG9w0BCQQxFgQU8B1vdRjk palAZkOVWcm0qC+26BgwUgYJKoZIhvcNAQkPMUUwQzAKBggqhkiG9w0DBzAOBggqhkiG9w0D AgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgYUGCSsGAQQB gjcQBDF4MHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENB AhBSrt/59KEyjYptbFxOQCILMIGHBgsqhkiG9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBSrt/59KEyjYptbFxOQCILMA0GCSqG SIb3DQEBAQUABIIBAF+QzwmDYri0+R+NpxnXycT22txNR9NbRp4Z+y2/rf20N6+E6kqS+aBh ipCIGNODH50MJrsRsJGYMNmpNB5TX0iUojPN0gRNLpoCdJRAUSuGphUDVZo6pWHRlbOQpmDN kPD608AqN+E3RMC9nGXOgKbHWxNV+loJCVf8X0aAFZlVPmVy3I8RlcBQPbY4FztHSs7HZ8UB a4YBarVoaF2Tk6+jf29giqkj+plnjN1nZ6vL71yjM2gE34dhZHDthtD4TffKhMEoyrhja94o g+VqaXVq/GpQgu0tvG7iWsXHprTSM8ibluOuBbk+3LBo5SL9Sk/iTmZ3K1VZaVdZOmRsJP8A AAAAAAA= --------------ms080306040807070507000306--