Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1b8oFC-0008Jq-C6 for pgsql-sql@arkaria.postgresql.org; Fri, 03 Jun 2016 12:30:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1b8oFB-0002yM-V6 for pgsql-sql@arkaria.postgresql.org; Fri, 03 Jun 2016 12:30:30 +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 1b8e8W-0000i4-Qt for pgsql-sql@postgresql.org; Fri, 03 Jun 2016 01:42:57 +0000 Received: from mail-lf0-x22e.google.com ([2a00:1450:4010:c07::22e]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1b8e8T-0007dh-Ly for pgsql-sql@postgresql.org; Fri, 03 Jun 2016 01:42:55 +0000 Received: by mail-lf0-x22e.google.com with SMTP id b73so45122994lfb.3 for ; Thu, 02 Jun 2016 18:42:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=clmOGq/Id40QV+nYi1UPXUZ9aB1LaJ4BEpUyRR5ZqH0=; b=cD5VvgZHGWSWbalV0wci5Oxs8NtL+TyDLIwOSazV4/9U9dsesdysbhHC3aPIpAqB72 GHSOquYKoLWkugjj5bouD4FLaSGsTcVPvjS0WAc8uKtN21U0oOb1DirKvkV+1zi/1arT sh6E6rbF4MMshSLJhIftyX14LS1MXZeUvGMZVCuBdwmaGdbd0FI264u1ocqRYEJqhZpB XgyI7oPZytSo2lkJ4HoKUkLsAvusqFwUbSv7DJYPtuelNZ7XP/bs7rNmuqJVEpbbQ72P eC/BQ+i9r4SXrD9nucK2tkpwRvJQGZJXn2c2K5vIjKtEpm8OebeUywAt6xJSLUXnRks1 L1qQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=clmOGq/Id40QV+nYi1UPXUZ9aB1LaJ4BEpUyRR5ZqH0=; b=f3jY2qbCHF3SwGSM5csJLWWW6p1Vrkx+v8vRHHKw2Q1q82Po4Kr2N1/oEt3uJniWYg OwJ99/0M8NvtnjxOLHxCjUtzG+YQ/dCIwSG6qgn5ZViK6mbDWWbgw6c79xT36+UqRbC5 evCWV+ddVQ3Q6O45wT2+SB34WiVwfs2qYuYyWGMmA4ch1tqFcMQJxThwVw+0V3BScKi4 TsFwHMOPnegIo6kRumtEqd9AiOr5WjvtI2X105PG9hYUHltzXLaVs93xzyar+y7FIJBJ DDPVBvBR71YBJZQPisdvj2DYeak2LinD8ziR/8Yuy0kFAtkECgpfKmaO4MiuKvbl/HOc 3THw== X-Gm-Message-State: ALyK8tIzr/pQQn9snHvGDIUWsRL/IYuCyyOy90iOU5wCt8D4ZjCruaRfiYSCxnMkqs70Eg== X-Received: by 10.25.139.136 with SMTP id n130mr222058lfd.203.1464918171810; Thu, 02 Jun 2016 18:42:51 -0700 (PDT) Received: from [192.168.1.44] ([5.18.52.191]) by smtp.gmail.com with ESMTPSA id 1sm156674ljf.5.2016.06.02.18.42.50 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Thu, 02 Jun 2016 18:42:51 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_3F5BB789-1608-4210-BC18-DB654907FB0A" Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) Subject: Re: From: Max Lipsky In-Reply-To: Date: Fri, 3 Jun 2016 04:42:49 +0300 Cc: postgres list Message-Id: <7FBA5CC1-157B-476F-980B-686824D477F6@gmail.com> References: <6C9B51CF-C8D0-416A-986D-AE1615A01909@gmail.com> To: Steve Midgley X-Mailer: Apple Mail (2.3124) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail=_3F5BB789-1608-4210-BC18-DB654907FB0A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Sorry, I send broken link New link >> https://www.dropbox.com/s/8g3tkrikeap89q9/text.zip?dl=3D0 = > On 03 Jun 2016, at 03:28, Steve Midgley wrote: >=20 > In order to answer questions like that, generally, it's super helpful = if you will include the "EXPLAIN" output for the query. It may also be = useful to share some simple DDL stuff so we can create the tables and = data you're using and try it out on our end. >=20 > In this specific case (without digging too much into this, and without = the info above) I'd guess there's an index missing and probably on the = field "realisation_category_id" >=20 > Steve >=20 > On Thu, Jun 2, 2016 at 2:31 PM, Max Lipsky > wrote: > Hi All! >=20 > Why is too much difference in time execution between these two = queries: >=20 >=20 > SELECT rc.id AS id, rc.name > FROM realisation_category rc > WHERE EXISTS ( > SELECT * FROM realisation r, post p WHERE = (r.realisation_category_id =3D rc.id AND r.site_id =3D = 1) > OR (p.realisation_category_id =3D rc.id AND = p.site_id =3D 1) > ) > [2016-06-03 01:23:12] 35 row(s) retrieved starting from 1 in 14s 591ms = (14s 612ms total) >=20 >=20 >=20 > SELECT rc.id AS id, rc.name > FROM realisation_category rc > WHERE EXISTS ( > SELECT * FROM realisation r WHERE r.realisation_category_id =3D = rc.id AND r.site_id =3D 1 > ) OR EXISTS ( > SELECT * FROM post p WHERE p.realisation_category_id =3D rc.id = AND p.site_id =3D 1 > ) > [2016-06-03 01:25:25] 35 row(s) retrieved starting from 1 in 64ms = (86ms total) >=20 > Thanks >=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 --Apple-Mail=_3F5BB789-1608-4210-BC18-DB654907FB0A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
Sorry, I send broken link
New link >> https://www.dropbox.com/s/8g3tkrikeap89q9/text.zip?dl=3D0


On 03 Jun 2016, at 03:28, Steve = Midgley <science@misuse.org> wrote:

In order to answer questions like that, generally, it's super = helpful if you will include the "EXPLAIN" output for the query. It may = also be useful to share some simple DDL stuff so we can create the = tables and data you're using and try it out on our end.
In this specific case (without digging = too much into this, and without the info above) I'd guess there's an = index missing and probably on the field "realisation_category_id"

Steve

On Thu, Jun 2, 2016 at 2:31 PM, = Max Lipsky <maxlipsky@gmail.com> wrote:
Hi All!

Why is too much difference in time execution between these two = queries:


SELECT rc.id AS id, rc.name
FROM   realisation_category rc
WHERE  EXISTS (
    SELECT * FROM realisation r, post p WHERE = (r.realisation_category_id =3D rc.id AND r.site_id = =3D 1)
    OR (p.realisation_category_id =3D rc.id AND p.site_id = =3D 1)
)
[2016-06-03 01:23:12] 35 row(s) retrieved starting from 1 in 14s 591ms = (14s 612ms total)



SELECT rc.id AS id, rc.name
FROM   realisation_category rc
WHERE  EXISTS (
    SELECT * FROM realisation r WHERE = r.realisation_category_id =3D rc.id AND r.site_id =3D 1
= ) OR EXISTS (
    SELECT * FROM post p WHERE p.realisation_category_id =3D = rc.id AND p.site_id =3D 1
)
[2016-06-03 01:25:25] 35 row(s) retrieved starting from 1 in 64ms (86ms = total)

Thanks

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


= --Apple-Mail=_3F5BB789-1608-4210-BC18-DB654907FB0A--