DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=hMd+aS3KfLxiYwiGioAN7glkw6t9oWfcDXsLAY/xgLjuQ+Jn+N6YJbpNTXDh77nBkf9V8QLFIT35n37rRRLENeOlHn49reuWnxIJASl1NVKm6JRk8JpzoNuEKowL7pKQSXvrxyj2Iu5mHZ6T2f+9EtMF0ygJ0bzKWeuZMThf9Ys=;
References: 
 <CAAXGW-wJjFtEygtoegnxKScMkedvCAP1FZKDd0Sm8EP=fq1WYQ@mail.gmail.com>
	<CAFjNrYvMZhbnwySEGmUsiuv9qWx8Nk_PM+HsF4R_CuyoUco44A@mail.gmail.com>
	<CAAXGW-zp7E5kggSNraUKncQ3FEmzhFAJzm9ctH2ad-MXXaopSQ@mail.gmail.com>
Message-ID: <1370269578.65701.YahooMailNeo@web162903.mail.bf1.yahoo.com>
Date: Mon, 3 Jun 2013 07:26:18 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: SQL performance
To: Robert DiFalco <robert.difalco@gmail.com>, Szymon Guz <mabewlun@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAAXGW-zp7E5kggSNraUKncQ3FEmzhFAJzm9ctH2ad-MXXaopSQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Robert DiFalco <robert.difalco@gmail.com> wrote:

> CREATE TABLE contacts
> (
>=A0=A0=A0=A0 id BIGINT PRIMARY KEY NOT NULL, // generated
>=20
>=A0=A0=A0=A0 blocked BOOL,
>=A0=A0=A0=A0 owner_id BIGINT NOT NULL,
>=A0=A0=A0=A0 user_id BIGINT,
>=A0=A0=A0=A0 FOREIGN KEY ( owner_id ) REFERENCES app_users ( id ) ON DELET=
E CASCADE,
>=20
>=A0=A0=A0=A0 FOREIGN KEY ( user_id ) REFERENCES app_users ( id ) ON DELETE=
 SET NULL
> );
> CREATE INDEX idx_contact_owner ON contacts ( owner_id );
> CREATE INDEX idx_contact_mutual ON contacts ( owner_id, user_id ) WHERE u=
ser_id IS NOT NULL AND NOT blocked;

Well, the first thing I note is that "blocked" can be NULL.=A0 You
exclude rows from the result where it IS NULL in either row.=A0 That
may be what you really want, but it seems worth mentioning.=A0 If you
don't need to support missing values there, you might want to add a
NOT NULL constraint.=A0 If it should be NULL when user_id is, but not
otherwise, you might want a row-level constraint.=A0 You might shave
a tiny amount off the runtime by getting rid of the redundant tests
for NOT NULL on user_id; it cannot compare as either TRUE on either
=3D or <> if either (or both) values are NULL.

> explain analyze verbose
> select c.user_id
> from contact_entity c left outer join contact_entity c1 on c1.owner_id =
=3D c.user_id and c1.user_id =3D c.owner_id
> where NOT c.blocked AND NOT c1.blocked AND c.owner_id =3D 24 AND c.user_i=
d !=3D 24
> AND c.user_id IS NOT NULL AND c1.user_id IS NOT NULL
> group by c.user_id;

> Group=A0 (cost=3D0.00..9.00 rows=3D1 width=3D8) (actual time=3D0.170..0.3=
01 rows=3D8 loops=3D1)
>=A0=A0 Output: c.user_id
>=A0=A0 ->=A0 Merge Join=A0 (cost=3D0.00..9.00 rows=3D1 width=3D8) (actual =
time=3D0.166..0.270 rows=3D17 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0 Output: c.user_id
>=A0=A0=A0=A0=A0=A0=A0=A0 Merge Cond: (c.user_id =3D c1.owner_id)
>=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using idx_contact_mutual on publ=
ic.contact_entity c=A0 (cost=3D0.00..5.10 rows=3D2 width=3D16) (actual time=
=3D0.146..0.164 rows=3D11 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Output: c.id, c.blocked, c.firs=
t_name, c.last_name, c.owner_id, c.user_id
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((c.owner_id =3D 24=
) AND (c.user_id IS NOT NULL))
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (c.user_id <> 24)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Rows Removed by Filter: 1
>=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using idx_contact_mutual on publ=
ic.contact_entity c1=A0 (cost=3D0.00..6.45 rows=3D1 width=3D16) (actual tim=
e=3D0.012..0.049 rows=3D18 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Output: c1.id, c1.blocked, c1.f=
irst_name, c1.last_name, c1.owner_id, c1.user_id
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((c1.user_id IS NOT=
 NULL) AND (c1.user_id =3D 24))
> Total runtime: 0.388 ms

> explain analyze verbose=20
> select c.user_id=20
> from contact_entity c=20
> where c.owner_id=3D24 and c.user_id<>24 and c.user_id IS NOT NULL and NOT=
 c.blocked and (exists(
>=A0=A0 select 1=20
>=A0=A0 from contact_entity c1=20
>=A0=A0 where NOT c1.blocked and c1.owner_id=3Dc.user_id and c1.user_id IS =
NOT NULL and c1.user_id=3Dc.owner_id))=20
> group by c.user_id;

> Group=A0 (cost=3D0.00..9.00 rows=3D1 width=3D8) (actual time=3D0.048..0.1=
59 rows=3D8 loops=3D1)
>=A0=A0 Output: c.user_id
>=A0=A0 ->=A0 Merge Semi Join=A0 (cost=3D0.00..9.00 rows=3D1 width=3D8) (ac=
tual time=3D0.044..0.137 rows=3D9 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0 Output: c.user_id
>=A0=A0=A0=A0=A0=A0=A0=A0 Merge Cond: (c.user_id =3D c1.owner_id)
>=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using idx_contact_mutual on publ=
ic.contact_entity c=A0 (cost=3D0.00..5.10 rows=3D2 width=3D16) (actual time=
=3D0.024..0.042 rows=3D11 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Output: c.id, c.blocked, c.firs=
t_name, c.last_name, c.owner_id, c.user_id
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((c.owner_id =3D 24=
) AND (c.user_id IS NOT NULL))
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (c.user_id <> 24)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Rows Removed by Filter: 1
>=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using idx_contact_mutual on publ=
ic.contact_entity c1=A0 (cost=3D0.00..6.45 rows=3D1 width=3D16) (actual tim=
e=3D0.011..0.047 rows=3D16 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Output: c1.id, c1.blocked, c1.f=
irst_name, c1.last_name, c1.owner_id, c1.user_id
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((c1.user_id IS NOT=
 NULL) AND (c1.user_id =3D 24))
> Total runtime: 0.224 ms

So, it looks like you can get about 3000 to 4000 of these per
second on a single connection -- at least in terms of server-side
processing.=A0 Were you expecting more than that?

--=20
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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