Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dDPzY-00022P-IA for pgsql-performance@arkaria.postgresql.org; Wed, 24 May 2017 06:41:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dDPzY-0000RU-1r for pgsql-performance@arkaria.postgresql.org; Wed, 24 May 2017 06:41:56 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dDPzW-0000ML-NG for pgsql-performance@postgresql.org; Wed, 24 May 2017 06:41:54 +0000 Received: from edmmai03.elster.de ([217.237.175.36]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84_2) (envelope-from ) id 1dDPzT-0007Pu-2p for pgsql-performance@postgresql.org; Wed, 24 May 2017 06:41:53 +0000 X-IronPort-AV: E=Sophos;i="5.38,384,1491256800"; d="p7s'?scan'208";a="2017356" Received: from edmzrf02.een.elster.de ([192.168.20.13]) by edmmai03.een.elster.de with ESMTP/TLS/DHE-RSA-AES256-GCM-SHA384; 24 May 2017 08:40:24 +0200 Received: from [10.200.136.1] (unknown [10.200.136.1]) by edmzrf02.een.elster.de (Postfix) with ESMTPSA id A922C3E00AA; Wed, 24 May 2017 08:41:46 +0200 (CEST) Subject: Re: select subquery versus join subquery To: Jeff Janes Cc: "pgsql-performance@postgresql.org" References: From: "Gunnar \"Nick\" Bluth" Message-ID: Date: Wed, 24 May 2017 08:41:37 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.1.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; protocol="application/pkcs7-signature"; micalg=sha-256; boundary="------------ms070408080903070004090700" X-Pg-Spam-Score: -1.9 (-) 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 cryptographically signed message in MIME format. --------------ms070408080903070004090700 Content-Type: text/plain; charset=utf-8 Content-Language: de-DE Content-Transfer-Encoding: quoted-printable Am 05/23/2017 um 06:59 PM schrieb Jeff Janes: > On Tue, May 23, 2017 at 4:03 AM, Gunnar "Nick" Bluth > > > wrote: 8>< ----- >=20 > Unfortunately that always uses the index scan, even at a high cutoff > where aggregation on the seq scan and then hash joining is more > appropriate. So it is very similar to view2, except that it doesn't > return the rows from "thing" which have zero corresponding rows in > thing_alias. >=20 > ***** > Note btw. that both view1 and view2 don't return any md5 values for= me, > while view3 does! > ***** >=20 >=20 > Because of the way I constructed the data, using the power transform of= > the uniform random distribution, the early rows of the view (if sorted > by thing_id) are mostly null in the md5 column, so if you only look at > the first few screen-fulls you might not see any md5. But your view > does effectively an inner join rather than a left join, so your view > gets rid of the rows with a NULL md5. Most things don't have aliases; > of the things that do, most have 1; and some have a several. D'oh, of course! My bad... shouldn't have looked at the results with LIMIT :-/ My next best guess would involve a MatView for the aggregates... --=20 Gunnar "Nick" Bluth DBA ELSTER Tel: +49 911/991-4665 Mobil: +49 172/8853339 --------------ms070408080903070004090700 Content-Type: application/pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgEFADCABgkqhkiG9w0BBwEAAKCC DCQwggVwMIIEWKADAgECAgZIjQJd1vQwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNVBAYTAkRF MSswKQYDVQQKDCJULVN5c3RlbXMgRW50ZXJwcmlzZSBTZXJ2aWNlcyBHbWJIMR8wHQYDVQQL DBZULVN5c3RlbXMgVHJ1c3QgQ2VudGVyMSUwIwYDVQQDDBxULVRlbGVTZWMgR2xvYmFsUm9v dCBDbGFzcyAyMB4XDTEyMTEyOTExMjM1NVoXDTI0MTEyOTIzNTk1OVowdTELMAkGA1UEBhMC REUxJTAjBgNVBAoTHFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFsIEdtYkgxHzAdBgNVBAsTFlQt U3lzdGVtcyBUcnVzdCBDZW50ZXIxHjAcBgNVBAMTFVRlbGVTZWMgQnVzaW5lc3MgQ0EgMTCC ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBANEw1E1Ew+RLWrQYng5RJ7WpYkl6GgbA cywRiVWSrA7L/kgvu13j9KmIuoFr5Ps3HSxX60WYVUFGicfnpzFke554CJ/AGD/pSmKxhPzg 0MytlS67gGr1p2TFN/vePeegzbGidDWjz+/tTXRtuo0kGrSf/FpVRjv/98LxeXp2d89P68oi jXbYBZn3BPE8n3zbqGnwVd+jQahJS25dIHENuosJbmsi8KSd81aJwNbAWDAIV+Zi3Y4/0L3j ujhXX63SNVK7q67Gtp8hEpOvRYtWNtXMpEw2LzPhtILmZEVd7urX3KBNQX/eZpmK/V1u7EXr dOIJE/aK6qhnKGpOn/gSrLUCAwEAAaOCAfYwggHyMA4GA1UdDwEB/wQEAwIBBjAdBgNVHQ4E FgQUhoiTH4vrVZZl1UcBvTeMfM2ox6MwHwYDVR0jBBgwFoAUv1kgNgB5oKAia4zV8mHSuCzL gkowEgYDVR0TAQH/BAgwBgEB/wIBADBTBgNVHSAETDBKMD4GCSsGAQQBvUcNGTAxMC8GCCsG AQUFBwIBFiNodHRwOi8vc2JjYS50ZWxlc2VjLmRlL2Nwcy9jcHMyLnBkZjAIBgZngQwBAgIw gfoGA1UdHwSB8jCB7zA6oDigNoY0aHR0cDovL2NybC5zYmNhLnRlbGVzZWMuZGUvcmwvR2xv YmFsUm9vdF9DbGFzc18yLmNybDCBsKCBraCBqoaBp2xkYXA6Ly9sZGFwLnNiY2EudGVsZXNl Yy5kZS9DTj1ULVRlbGVTZWMlMjBHbG9iYWxSb290JTIwQ2xhc3MlMjAyLE9VPVQtU3lzdGVt cyUyMFRydXN0JTIwQ2VudGVyLE89VC1TeXN0ZW1zJTIwRW50ZXJwcmlzZSUyMFNlcnZpY2Vz JTIwR21iSCxDPURFP0F1dGhvcml0eVJldm9jYXRpb25MaXN0MDoGCCsGAQUFBwEBBC4wLDAq BggrBgEFBQcwAYYeaHR0cDovL29jc3AwNC50ZWxlc2VjLmRlL29jc3ByMA0GCSqGSIb3DQEB CwUAA4IBAQCThdHrhHoZWcglgDokpbmWcQab/tYOdOBA3dXNXR0ggBlDSQ94dt4j3lzMN+2j ynxKIQTZJZGZsT8HsmRC3BTnpY/T7a0gbXkdUiwXspLQs18wL0Q+Vu80V7aHkKm8R3WIMpM+ 72lLGkowDbHnDUMBZYd8N4+bStWrUcn29ZfDHj9eFJSutg1yWfzl1xdfxOJmTtpmjSjRCtc7 tixaok7W2O+SlS1lmSRoMIqCDWLO34f//Dj1hHrOx4xxRnojKX2s9lYoAqyAi69XoEgdiGGf Sj7/37yhZp0aAYbzHBFVy1rS/DC5FNYrCQfsauP2jVEaRClHbpeOwTHWNQj4HiRPMIIGrDCC BZSgAwIBAgIJAOgv2m5i1ojKMA0GCSqGSIb3DQEBCwUAMHUxCzAJBgNVBAYTAkRFMSUwIwYD VQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMR8wHQYDVQQLExZULVN5c3RlbXMg VHJ1c3QgQ2VudGVyMR4wHAYDVQQDExVUZWxlU2VjIEJ1c2luZXNzIENBIDEwHhcNMTcwMjAx MTI1MjI4WhcNMjAwMjAxMjM1OTU5WjCBujELMAkGA1UEBhMCREUxKzApBgNVBAoMIkJheWVy aXNjaGVzIExhbmRlc2FtdCBmw7xyIFN0ZXVlcm4xEjAQBgNVBAsTCWVsc3Rlci5kZTEZMBcG A1UECxMQTWFpbC1aZXJ0aWZpa2F0ZTEVMBMGA1UEAxMMR3VubmFyIEJsdXRoMSwwKgYJKoZI hvcNAQkBFh1ndW5uYXIuYmx1dGguZXh0ZXJuQGVsc3Rlci5kZTEKMAgGA1UEBRMBMTCCASIw DQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMOv2jzaRaBBdpWKT/iwVk6M7BjwFOLjGi+z sfbuaiXE57PQqOy2+TRR+78VQk9pV1vb8UErJvqi/gdpizGk8+zyjcqhVVYKKwvLUWEIeOiT RGH+ueA14K/ljuhT2W6SbaUUom/Ln0AJC4S747raNXo8qVILuymzqAJIE4hrU0HaN2vEG4aI cCldrlNHarX5g3yXYllNRS9A29jVo0c6wRuWLt4hy4S0mxtpTbuFAOV17o2QTwRDQGAukf6x wynBQlKYVp4Mg5Mpj30N2sii6R4e+XDc8FrpJw4wmgqbPWbpFkUdiLuqeScARqY5F3DELmwc dlO1AJ8CmBw/r8tCSxsCAwEAAaOCAvcwggLzMB8GA1UdIwQYMBaAFIaIkx+L61WWZdVHAb03 jHzNqMejMB0GA1UdDgQWBBQ5Cl1MmzPCejbOjWyM1Bmw/6vFHjAOBgNVHQ8BAf8EBAMCBaAw TgYDVR0gBEcwRTBDBgkrBgEEAb1HDRkwNjA0BggrBgEFBQcCARYoaHR0cDovL3NiY2EudGVs ZXNlYy5kZS9kb3dubG9hZC9jcHMyLnBkZjAJBgNVHRMEAjAAMIHwBgNVHR8EgegwgeUwPaA7 oDmGN2h0dHA6Ly9jcmwuc2JjYS50ZWxlc2VjLmRlL3JsL1RlbGVTZWNfQnVzaW5lc3NfQ0Ff MS5jcmwwgaOggaCggZ2GgZpsZGFwOi8vbGRhcC5zYmNhLnRlbGVzZWMuZGUvQ049VGVsZVNl YyUyMEJ1c2luZXNzJTIwQ0ElMjAxLE9VPVQtU3lzdGVtcyUyMFRydXN0JTIwQ2VudGVyLE89 VC1TeXN0ZW1zJTIwSW50ZXJuYXRpb25hbCUyMEdtYkgsQz1ERT9DZXJ0aWZpY2F0ZVJldm9j YXRpb25MaXN0MIIBJwYIKwYBBQUHAQEEggEZMIIBFTAvBggrBgEFBQcwAYYjaHR0cDovL29j c3AwMy5zYmNhLnRlbGVzZWMuZGUvb2NzcHIwRAYIKwYBBQUHMAKGOGh0dHA6Ly9jcmwuc2Jj YS50ZWxlc2VjLmRlL2NydC9UZWxlU2VjX0J1c2luZXNzX0NBXzEuY2VyMIGbBggrBgEFBQcw AoaBjmxkYXA6Ly9sZGFwLnNiY2EudGVsZXNlYy5kZS9jbj1UZWxlU2VjJTIwQnVzaW5lc3Ml MjBDQSUyMDEsb3U9VC1TeXN0ZW1zJTIwVHJ1c3QlMjBDZW50ZXIsbz1ULVN5c3RlbXMlMjBJ bnRlcm5hdGlvbmFsJTIwR21iSCxjPWRlP2NBQ2VydGlmaWNhdGUwKAYDVR0RBCEwH4EdZ3Vu bmFyLmJsdXRoLmV4dGVybkBlbHN0ZXIuZGUwDQYJKoZIhvcNAQELBQADggEBAKZlYP/Xuqb5 DEwFRzdCeqCYoBWAO/U4dhEAkxHZEIuJSFLg3df6+p4i8CasI84FTtsDVbXxnsoFYXBWtk6A L8cQKL/b/VA8idZpNrIHAc0E4Eowzy4p+Q0a17CwN0E6RItw9Slr/oBflhEJWixCtoPoFRDD UMr0XE0EwIchQn2yzMnU5jZSu2MRdxr1fIl89g7Cw0XDUM/wIJg7klZtKTdmc69f5LBjI08W v5EF20oeQAHobnWLS5VjVTtzLYGME4tgGvpD8bg4y97PtDFhMg4YW2yKm3SXqd/w9StVmjdS W5IWyhqO2uDgvcdxGj0TRuYg02J1F7UxoXUeGYr8CZgxggO3MIIDswIBATCBgjB1MQswCQYD VQQGEwJERTElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEfMB0GA1UE CxMWVC1TeXN0ZW1zIFRydXN0IENlbnRlcjEeMBwGA1UEAxMVVGVsZVNlYyBCdXNpbmVzcyBD QSAxAgkA6C/abmLWiMowDQYJYIZIAWUDBAIBBQCgggIFMBgGCSqGSIb3DQEJAzELBgkqhkiG 9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTE3MDUyNDA2NDEzN1owLwYJKoZIhvcNAQkEMSIEIHL6 +0kz19MC30EWtd/0nOfZkzI75iWfOzo/uPFZFw/hMGwGCSqGSIb3DQEJDzFfMF0wCwYJYIZI AWUDBAEqMAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZI hvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgZMGCSsGAQQBgjcQBDGBhTCBgjB1 MQswCQYDVQQGEwJERTElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEf MB0GA1UECxMWVC1TeXN0ZW1zIFRydXN0IENlbnRlcjEeMBwGA1UEAxMVVGVsZVNlYyBCdXNp bmVzcyBDQSAxAgkA6C/abmLWiMowgZUGCyqGSIb3DQEJEAILMYGFoIGCMHUxCzAJBgNVBAYT AkRFMSUwIwYDVQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMR8wHQYDVQQLExZU LVN5c3RlbXMgVHJ1c3QgQ2VudGVyMR4wHAYDVQQDExVUZWxlU2VjIEJ1c2luZXNzIENBIDEC CQDoL9puYtaIyjANBgkqhkiG9w0BAQEFAASCAQAC89CsOKuLpJZNOyyAScZXs/L9pukjoqfq exzrUuEzNzbN0v24lkNR4XQ8ml/QACDu4aZ1FcUt6g0nkVNoTA99VvHCnhw4cy7UMAZ1HkaK Pj4O2C6UPytoW3ZdHCUs80KJnzS4r2JSHjPz9E030JPOwwLWCbf8KIdfRpdQl9kJLRFPJgTo OqJ/AzwCP62ElnVquUwG+RlVvyBWo8jw97a9n/9VvJnRL2Gl+L3Y4B9hMVb125160nmdiU5A IrZJrWybS/XVH7t2FW1O3AgpJG8YWLMvTEFdBZWtTMAlCXMAFBDf/X1bLP0V7CbJsOxiHcAb 7zeEcXgTrmHfoJh2Jab1AAAAAAAA --------------ms070408080903070004090700--