Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1b8oEz-0008EZ-7b for pgsql-sql@arkaria.postgresql.org; Fri, 03 Jun 2016 12:30:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1b8oEy-0002QY-Qj for pgsql-sql@arkaria.postgresql.org; Fri, 03 Jun 2016 12:30:16 +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 1b8drC-00005h-Bw for pgsql-sql@postgresql.org; Fri, 03 Jun 2016 01:25:02 +0000 Received: from mail-lf0-x242.google.com ([2a00:1450:4010:c07::242]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1b8dr8-00071A-Ux for pgsql-sql@postgresql.org; Fri, 03 Jun 2016 01:25:01 +0000 Received: by mail-lf0-x242.google.com with SMTP id q63so6649449lfi.2 for ; Thu, 02 Jun 2016 18:24:58 -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=jVYquFtmdyIrG2lomIOO0vOHR542DSOiZ5XS1jchVs4=; b=IeHj9sI7e/zmWEw/1iTqjz2FEfZLGyVGOQw5YPo9NnH0lXaagIulyKVrmB3+dCopRc avCDK8pLOIOfSeA/Qp68N4YeQqw1+EkMq1e6M/7Qa/I0MfIJGwAZaU9tBbAKsJmes3Jl aQXbZPLNqxh8Pon2rw2CDtbZxJ+oMBI7tuNzVE9ZFbkseSTLjeFCG5utHQhYJMsKClPe jMVbDCHZuH9W9iXLcY+PvBOisE7LpmcI/KZU9bYeTXTjoQCNh5E09Gnqs24gvzOaD5eP jWuB3CDSRoeO0/zTarhmsSIr2THwubMtAdhZht5IACI6G0yBuKtuD/M/dQga8n2srpP+ fbfw== 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=jVYquFtmdyIrG2lomIOO0vOHR542DSOiZ5XS1jchVs4=; b=BI9HPEsAwCIntOs/CqibG06YcH6fAMrsHYHQjkg299bfM3pl425WNnG9PRVZwi5ZtH N3515nIFcQMsobaDZy2oVCs5zKdqvcPsT3JZfayLt7Zw+/oQF4Kj4Co86sm6wnjqD7iD BczfzSVIB68bfnT4HPAj6h97SmqKxjbJVOFqU8aKdMsIs4OQLmq3CcTFmQOrK2DxMwOs 2LI2idc64CYK8TxbQweyZUW74O0gu050TNhrHrEyl3Mi6jLSx7LTBq3PUDq1tRTlc6Ms dTh08IUFj4cHpXu7Ck/jdjP2zDnsAR+F4P9t6qRIoHkjS8+JQru1WKpSoLXij49PWKEm oiiQ== X-Gm-Message-State: ALyK8tJKmdtb7jkO8nX6q8NVLYN2CphyfW59YbHZMIbvOOgZ2Rh/tJ5yjtDT65Y83knRrg== X-Received: by 10.46.33.10 with SMTP id h10mr234628ljh.35.1464917096404; Thu, 02 Jun 2016 18:24:56 -0700 (PDT) Received: from [192.168.1.44] ([5.18.52.191]) by smtp.gmail.com with ESMTPSA id r5sm299786lbb.14.2016.06.02.18.24.55 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Thu, 02 Jun 2016 18:24:55 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_BA82BF29-2579-474C-A303-FCA515B6B253" 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:24:53 +0300 Cc: postgres list Message-Id: <91D59F0A-7228-49FA-940F-597EEFB97D30@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=_BA82BF29-2579-474C-A303-FCA515B6B253 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Thank you for your answer, you can download test DB >> = https://www.dropbox.com/s/uhcw3syyf5yfht0/test.zip?dl=3D0 = Slow: 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 AND rc.parent_id is not null) OR = (p.realisation_category_id =3D rc.id AND p.site_id =3D 1 AND = rc.parent_id is not null) ); Nested Loop Semi Join (cost=3D0.00..7099949.93 rows=3D5 width=3D520) Join Filter: (((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))) -> Seq Scan on realisation_category rc (cost=3D0.00..2.44 rows=3D44 = width=3D520) Filter: (parent_id IS NOT NULL) -> Materialize (cost=3D0.00..145886.48 rows=3D6511689 width=3D16) -> Nested Loop (cost=3D0.00..81532.04 rows=3D6511689 = width=3D16) -> Seq Scan on post p (cost=3D0.00..78.81 rows=3D3681 = width=3D8) -> Materialize (cost=3D0.00..61.53 rows=3D1769 width=3D8)= -> Seq Scan on realisation r (cost=3D0.00..52.69 = rows=3D1769 width=3D8) Fast: 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 AND rc.parent_id is not null ) OR EXISTS ( SELECT * FROM post p WHERE p.realisation_category_id =3D rc.id AND = p.site_id =3D 1 AND rc.parent_id is not null ); Seq Scan on realisation_category rc (cost=3D0.00..1041.12 rows=3D33 = width=3D520) Filter: ((SubPlan 1) OR (SubPlan 2)) SubPlan 1 -> Result (cost=3D0.00..61.53 rows=3D6 width=3D0) One-Time Filter: (rc.parent_id IS NOT NULL) -> Seq Scan on realisation r (cost=3D0.00..61.53 rows=3D6 = width=3D0) Filter: ((realisation_category_id =3D rc.id) AND = (site_id =3D 1)) SubPlan 2 -> Result (cost=3D9.34..13.35 rows=3D1 width=3D0) One-Time Filter: (rc.parent_id IS NOT NULL) -> Bitmap Heap Scan on post p (cost=3D9.34..13.35 rows=3D1 = width=3D0) Recheck Cond: ((site_id =3D 1) AND = (realisation_category_id =3D rc.id)) -> BitmapAnd (cost=3D9.34..9.34 rows=3D1 width=3D0) -> Bitmap Index Scan on idx_5a8a6c8df6bd1646 = (cost=3D0.00..4.37 rows=3D12 width=3D0) Index Cond: (site_id =3D 1) -> Bitmap Index Scan on idx_5a8a6c8dd4f97f1a = (cost=3D0.00..4.71 rows=3D58 width=3D0) Index Cond: (realisation_category_id =3D = rc.id) > 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=_BA82BF29-2579-474C-A303-FCA515B6B253 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
Thank you for your answer,
you can download test DB >> https://www.dropbox.com/s/uhcw3syyf5yfht0/test.zip?dl=3D0


Slow:

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 = AND rc.parent_id is not null)
    =                     =                     =   OR (p.realisation_category_id =3D = rc.id AND p.site_id =3D 1 = AND rc.parent_id is not null)
);

Nested Loop Semi Join =  (cost=3D0.00..7099949.93 rows=3D5 width=3D520)
   Join Filter: (((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)))
   -> =  Seq Scan on realisation_category rc  (cost=3D0.00..2.44 = rows=3D44 width=3D520)
        =  Filter: (parent_id IS NOT NULL)
  =  ->  Materialize  (cost=3D0.00..145886.48 rows=3D6511689= width=3D16)
        =  ->  Nested Loop  (cost=3D0.00..81532.04 rows=3D6511689 = width=3D16)
          =      ->  Seq Scan on post p =  (cost=3D0.00..78.81 rows=3D3681 width=3D8)
               -> =  Materialize  (cost=3D0.00..61.53 rows=3D1769 = width=3D8)
          =            ->  Seq Scan on = realisation r  (cost=3D0.00..52.69 rows=3D1769 = width=3D8)

Fast:

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 = AND rc.parent_id is not null
) OR EXISTS (
  =   SELECT * FROM post = p WHERE p.realisation_category_id =3D = rc.id AND p.site_id =3D 1 = AND rc.parent_id is not null
);


Seq Scan on = realisation_category rc  (cost=3D0.00..1041.12 rows=3D33 = width=3D520)
   Filter: ((SubPlan 1) OR = (SubPlan 2))
   SubPlan 1
     ->  Result =  (cost=3D0.00..61.53 rows=3D6 width=3D0)
  =          One-Time Filter: (rc.parent_id IS NOT = NULL)
          =  ->  Seq Scan on realisation r  (cost=3D0.00..61.53 = rows=3D6 width=3D0)
        =          Filter: ((realisation_category_id =3D = rc.id) AND (site_id =3D 1))
   SubPlan = 2
     ->  Result =  (cost=3D9.34..13.35 rows=3D1 width=3D0)
  =          One-Time Filter: (rc.parent_id IS NOT = NULL)
          =  ->  Bitmap Heap Scan on post p  (cost=3D9.34..13.35 = rows=3D1 width=3D0)
        =          Recheck Cond: ((site_id =3D 1) AND = (realisation_category_id =3D rc.id))
    =              ->  BitmapAnd =  (cost=3D9.34..9.34 rows=3D1 width=3D0)
  =                     =  ->  Bitmap Index Scan on idx_5a8a6c8df6bd1646 =  (cost=3D0.00..4.37 rows=3D12 width=3D0)
  =                     =        Index Cond: (site_id =3D 1)
                =        ->  Bitmap Index Scan on = idx_5a8a6c8dd4f97f1a  (cost=3D0.00..4.71 rows=3D58 = width=3D0)
          =                   =  Index Cond: (realisation_category_id =3D rc.id)





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=_BA82BF29-2579-474C-A303-FCA515B6B253--