Subject: Re: select subquery versus join subquery
To: pgsql-performance@postgresql.org
References: 
 <CAMkU=1zxu+xZ7X44FpZ=Syot_STrUiGR9miB8K-HCqvb=KMtwA@mail.gmail.com>
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth.extern@elster.de>
Message-ID: <a7cc091e-b73e-389e-739d-c7d5b480d58d@elster.de>
Date: Tue, 23 May 2017 13:03: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: 
 <CAMkU=1zxu+xZ7X44FpZ=Syot_STrUiGR9miB8K-HCqvb=KMtwA@mail.gmail.com>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha-256; boundary="------------ms080007050708000902010804"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a cryptographically signed message in MIME format.

--------------ms080007050708000902010804
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable

Am 05/22/2017 um 09:57 PM schrieb Jeff Janes:
> I need to make a view which decorates rows from a parent table with
> aggregated values from a child table.  I can think of two ways to write=

> it, one which aggregates the child table to make a new query table and
> joins the parent to that, as shown in "view1" below.  Or does subselect=

> in the select list to aggregate just the currently matching rows, and
> returns that value, as in "view2" below.
>=20
> While these two are semantically equivalent, the planner doesn't
> understand that, and always executes them pretty much the way you would=

> naively do it based on the text of the query.
>=20
> But view1 is pretty slow if the WHERE clause is highly selective (like
> "WHERE cutoff<0.00001") because it has to summarize the entire child
> table just to pull out a few rows.  But view2 is pretty slow if the
> entire view or most of it (like "WHERE cutoff<0.9") is being returned.
>=20
> Is there some 3rd way to write the query which allows the planner to
> switch between strategies (summarize whole table vs summarize values on=

> demand) depending on the known selectivity of the where clause?
>=20
> In this case, the planner is getting the relative cost estimates roughl=
y
> correct.  It is not a problem of mis-estimation.
>=20
> I can always create two views, view_small and view_large, and swap
> between them based on my own knowledge of how restrictive a query is
> likely to be, but that is rather annoying.  Especially in the real-worl=
d
> situation, which is quite a bit more complex than this.
>=20
> create table thing as select x as id, random() as cutoff from
> generate_series(1,2000000) f(x);
>=20
> create table thing_alias as select
> floor(power(random()*power(2000000,5),0.2))::int thing_id, md5(x::text)=
,
> random() as priority from generate_series(1,150000) f(x);
>=20
> create index on thing_alias (thing_id );
>=20
> create index on thing (cutoff );
>=20
> vacuum; analyze;
>=20
> create view view1 as select id, md5,cutoff from thing left join=20
>   (
>      select distinct on (thing_id) thing_id, md5 from thing_alias=20
>      order by thing_id, priority desc
>   ) as foo=20
>   on (thing_id=3Did);
>=20
> create view view2 as select id,=20
>   (
>      select md5 from thing_alias where thing_id=3Did=20
>         order by priority desc limit 1
>   ) as md5,=20
>   cutoff from thing;
>=20
> Cheers,
>=20
> Jeff

Hi Jeff,

how does something like

CREATE OR REPLACE VIEW public.view3 AS
 SELECT thing.id,
    foo.md5,
    thing.cutoff
   FROM thing,
    LATERAL ( SELECT DISTINCT ON (thing_alias.thing_id)
thing_alias.thing_id,
            thing_alias.md5
           FROM thing_alias
          WHERE thing_alias.thing_id =3D thing.id
          ORDER BY thing_alias.thing_id, thing_alias.priority DESC) foo

work for you? At least that's always using an index scan here, as
opposed to view1, which (for me) defaults to a SeqScan on thing_alias at
a low cutoff.

*****
Note btw. that both view1 and view2 don't return any md5 values for me,
while view3 does!
*****

Results (ms, median of 3 runs):
cutoff<  0.1   0.9
view1:   348   1022
view2:   844   6484
view3:   842   5976

With

 LATERAL ( SELECT string_agg(thing_alias.md5, ','::text) AS md5
           FROM thing_alias
          WHERE thing_alias.thing_id =3D thing.id
          GROUP BY thing_alias.thing_id) foo

(which seems to make more sense ;-)

I yield 483 (0.1) and 3410 (0.9) ms (and return md5-Aggregates).

Cheers,
--=20
Gunnar "Nick" Bluth
DBA ELSTER

Tel:   +49 911/991-4665
Mobil: +49 172/8853339


--------------ms080007050708000902010804
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
9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTE3MDUyMzExMDMzN1owLwYJKoZIhvcNAQkEMSIEIP6U
7A8SdCNHIIbxSgaON/aM5iV7HrJsilZomQU9IzOfMGwGCSqGSIb3DQEJDzFfMF0wCwYJYIZI
AWUDBAEqMAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZI
hvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgZMGCSsGAQQBgjcQBDGBhTCBgjB1
MQswCQYDVQQGEwJERTElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEf
MB0GA1UECxMWVC1TeXN0ZW1zIFRydXN0IENlbnRlcjEeMBwGA1UEAxMVVGVsZVNlYyBCdXNp
bmVzcyBDQSAxAgkA6C/abmLWiMowgZUGCyqGSIb3DQEJEAILMYGFoIGCMHUxCzAJBgNVBAYT
AkRFMSUwIwYDVQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMR8wHQYDVQQLExZU
LVN5c3RlbXMgVHJ1c3QgQ2VudGVyMR4wHAYDVQQDExVUZWxlU2VjIEJ1c2luZXNzIENBIDEC
CQDoL9puYtaIyjANBgkqhkiG9w0BAQEFAASCAQCdU4VgDIEZlYlW8qE41167ewXBVl22VNSJ
/PBS0LndOLoELcmwKKnyO2zXsL+fR0o3erU4wjsKahw6u2lOR/QknFnPt1n5OjEehNgSpYcC
X+yrbn14EPl4IPQQbtfvVcP2hUE5I8w/jmWVF+tb2F3ZU+m1azC9sBkZ2Os+8lViqFsLox91
AqCmubCWC2v1/W/2RkgHnZ2HSiZIAa4hnvAZScOgjm763MCr3kD3Ln2GcvlZfcYORVFkeVDa
oURKxGpKdSTCmY36VCEk4HsBbfkMeCgllngZ31OoGipuYhNmbTd4VpKQE8px/WmCnQQ5aYhq
7ztR4TK47Q6aD++6yl6+AAAAAAAA
--------------ms080007050708000902010804--