Subject: Re: slow sub-query problem
References: <546A3FBA.9020901@gmail.com>
 <1416249886486-5827275.post@n5.nabble.com>
 <CAKFQuwbaGBD=QMUqb=51gawxK8G6NOiGzm6-fT2efX0kHCpDtw@mail.gmail.com>
 <546B09F3.90609@gmail.com> <1416361021024-5827453.post@n5.nabble.com>
From: daku.sandor@gmail.com
Content-Type: text/plain;
	charset=us-ascii
In-Reply-To: <1416361021024-5827453.post@n5.nabble.com>
Message-Id: <653A2F06-93F6-4FA1-BBE8-9353BB427503@gmail.com>
Date: Wed, 19 Nov 2014 10:27:08 +0100
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (1.0)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Slightly off:

I prefer "exists" to "join" if it's possible while on the list I almost nev=
er see any answer that uses "exists". Is my exists fixation is some kind of=
 bad practice?

Sandor Daku

> On 19 Nov 2014, at 02:37, David G Johnston <david.g.johnston@gmail.com> w=
rote:
>=20
> Tim Dudgeon wrote
>> SELECT t1.id, t1.structure_id, t1.batch_id,=20
>> t1.property_id, t1.property_data
>> FROM chemcentral.structure_props t1
>> JOIN chemcentral.structure_props t2 ON t1.id =3D t2.id=20
>> WHERE t2.structure_id IN (SELECT structure_id FROM=20
>> chemcentral.structure_props WHERE property_id =3D 643413)
>> AND t1.property_id IN (1, 643413, 1106201)
>> ;
>=20
> What about:
>=20
> SELECT t1.id, t1.structure_id, t1.batch_id, t1.property_id, t1.property_d=
ata
> FROM chemcentral.structure_props t1
> JOIN (
> SELECT DISTINCT super.id FROM chemcentral.structure_props super
> WHERE super.structure_id IN (
> SELECT sub.structure_id
> FROM chemcentral.structure_props sub
> WHERE sub.property_id =3D 643413
> )
> ) t2 ON (t1.id =3D t2.id)=20
> WHERE t1.property_id IN (1, 643413, 1106201)
> ;
>=20
> ?
>=20
> I do highly suggest using column table prefixes everywhere in this kind of
> query...
>=20
> Also, AND =3D=3D INTERSECT so:
>=20
> SELECT ... FROM chemcentral.structure_props WHERE property_id IN
> (1,643413,1106201)
> INTERSECT DISTINCT
> SELECT ... FROM chemcentral.structure_props WHERE structure_id IN (SELECT
> ... WHERE property_id =3D 643413)
>=20
> You can even use CTE/WITH expressions and give these subqueries meaningful
> names.
>=20
> David J.
>=20
>=20
>=20
>=20
> --
> View this message in context: http://postgresql.nabble.com/slow-sub-query=
-problem-tp5827273p5827453.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>=20
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql