Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YcsKW-0001pD-Lx for pgsql-performance@arkaria.postgresql.org; Tue, 31 Mar 2015 09:19:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YcsKW-0001l7-2J for pgsql-performance@arkaria.postgresql.org; Tue, 31 Mar 2015 09:19:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YcsKU-0001kz-1g for pgsql-performance@postgresql.org; Tue, 31 Mar 2015 09:19:26 +0000 Received: from mail-wg0-f47.google.com ([74.125.82.47]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YcsKP-0000gk-JT for pgsql-performance@postgresql.org; Tue, 31 Mar 2015 09:19:24 +0000 Received: by wgdm6 with SMTP id m6so11945783wgd.2 for ; Tue, 31 Mar 2015 02:19:19 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:content-type:thread-index:content-language; bh=vAhdlVEUoBqRj4IrJg01Rs1aUtne2OIzFLLU4mQ5DxE=; b=elcOQtNHmIsXqahqzD1gh1fzwBlah0au+mxZ3aeRkl5m9GoXsAmnmYpeQg8bVPvBNc EkuXE6PvqCs3LyMZ7YyhdzXeEJjg1GgjOZHuZIC5TkoX8fIIW0yfbvepmYHS0ekWl1xY duev2MrZUqWlWy0zfR2Vso6MLtA794IZOWE2akCEY4R72AiADVoNiEs+XPmsHJ0FiR4e vgjPq7TLw9/nVy7kerTIsRulHKjKMTX0rO4GdgD57zv3u2liDeVFt7Xv+uH60A6jptSj bbh7jk4xwICfeR9SwDYqa1xDlZo4L+6L5hjZtRNP/DKe0YhDi2+H36nS/kVrTc8VAsc+ c8UA== X-Gm-Message-State: ALoCoQnCiBndmoEIHbj78Nv0YLuDOdrqMCrSlG9aNcZ7Fndqwmip2OXcC9OopDCP40dfRTZwm8bL X-Received: by 10.180.104.66 with SMTP id gc2mr3775316wib.34.1427793559667; Tue, 31 Mar 2015 02:19:19 -0700 (PDT) Received: from RI02980T440p (jh.rocket-internet.de. [217.110.53.72]) by mx.google.com with ESMTPSA id w8sm19461969wja.4.2015.03.31.02.19.18 (version=TLSv1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Tue, 31 Mar 2015 02:19:18 -0700 (PDT) From: "Kevin Viraud" To: "'Pavel Stehule'" Cc: References: <00a001d06b90$2d1bbe80$87533b80$@rocket-internet.de> In-Reply-To: Subject: Re: Weird CASE WHEN behaviour causing query to be suddenly very slow Date: Tue, 31 Mar 2015 11:19:18 +0200 Message-ID: <00af01d06b93$c49862b0$4dc92810$@rocket-internet.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00B0_01D06BA4.8829BE30" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQECQJ6lxjgMF7czE/gsntwULz0zEwGn6Y5wnsVlF/A= Content-Language: de X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_00B0_01D06BA4.8829BE30 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi Pavel, =20 Thanks for your answer. =20 Yes sure, I could do that, but like I wrote the purpose is not to find a = way to rewrite it. But to understand why at a certain point it is = totally going off. I=E2=80=99m aware that the longer my case when will = be the longest the query will run. But 10x slower for adding one = condition, something feels wrong here. =20 Plus, the case when is part of a function so basically I use it this way = : SELECT col1, col2, get_channel(company_id, source_id, =E2=80=A6) FROM mytable; =20 Get_channel is coming from another app. And even though I have, I need = to assume that I don=E2=80=99t have the control over this one and that = I=E2=80=99m using it as if. =20 This is only my debugging query. =20 Best regards, =20 Kevin =20 From: Pavel Stehule [mailto:pavel.stehule@gmail.com]=20 Sent: Dienstag, 31. M=C3=A4rz 2015 11:09 To: Kevin Viraud Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Weird CASE WHEN behaviour causing query to be = suddenly very slow =20 Hi long CASE can be problem. Why you don't use a dictionary table and join? Regards Pavel =20 2015-03-31 10:53 GMT+02:00 Kevin Viraud >: Hi, =20 I have an issue with a rather large CASE WHEN and I cannot figure out = why it is so slow... =20 First, here is my test query : =20 SELECT CASE WHEN dwh_company_id =3D 1 = THEN CASE =20 = WHEN wv.source ~ '^$' THEN 'Not tracked' = WHEN wv.source ~ '^1$' THEN 'Not tracked1' = WHEN wv.source ~ '^2$' THEN 'Not tracked2' = WHEN wv.source ~ '^3$' THEN 'Not tracked3' = WHEN wv.source ~ '^4$' THEN 'Not tracked4' = WHEN wv.source ~ '^5$' THEN 'Not tracked5' = WHEN wv.source ~ '^6$' THEN 'Not tracked6' = WHEN wv.source ~ '^7$' THEN 'Not tracked7' = WHEN wv.source ~ '^8$' THEN 'Not tracked8' = WHEN wv.source ~ '^9$' THEN 'Not tracked9' = WHEN wv.source ~ '^10$' THEN 'Not tracked10' = WHEN wv.source ~ '^11$' THEN 'Not tracked11' = WHEN wv.source ~ '^12$' THEN 'Not tracked12' = WHEN wv.source ~ '^13$' THEN 'Not tracked13' = WHEN wv.source ~ '^14$' THEN 'Not tracked14' = WHEN wv.source ~ '^15$' THEN 'Not tracked15' = WHEN wv.source ~ '^16$' THEN 'Not tracked16' = WHEN wv.source ~ '^17$' THEN 'Not tracked17' = WHEN wv.source ~ '^18$' THEN 'Not tracked18' = WHEN wv.source ~ '^19$' THEN 'Not tracked19' = WHEN wv.source ~ '^20$' THEN 'Not tracked20' = WHEN wv.source ~ '^21$' THEN 'Not tracked21' = WHEN wv.source ~ '^22$' THEN 'Not tracked22' = WHEN wv.source ~ '^23$' THEN 'Not tracked23' = WHEN wv.source ~ '^24$' THEN 'Not tracked24' = WHEN wv.source ~ '^25$' THEN 'Not tracked25' = WHEN wv.source ~ '^26$' THEN 'Not tracked26' = WHEN wv.source ~ '^27$' THEN 'Not tracked27' = WHEN wv.source ~ '^28$' THEN 'Not tracked28' = --WHEN wv.source ~ '^29$' THEN 'Not tracked29' = WHEN wv.source ~ '^30$' THEN 'Not tracked30' = WHEN wv.source ~ '^31$' THEN 'Not tracked31' = WHEN wv.source ~ '^32$' THEN 'Not tracked32' = END ELSE 'Others' END as channel FROM ( SELECT wv.id , wv.ga_id,=20 split_part(wv.ga_source_medium, ' = / ', 1) as source, ga.dwh_source_id, s.dwh_company_id FROM marketing.web_visits wv=20 INNER JOIN dwh_metadata.google_analytics = ga ON ga.ga_id =3D wv.ga_id INNER JOIN dwh_manager.sources s ON = ga.dwh_source_id =3Ds.dwh_source_id --WHERE s.dwh_company_id =3D 1 LIMIT 100000 ) wv =20 =20 This is a pretty simple case, my subquery (or CTE when using WITH = statement) should return 5 fields with more or less this structure : Id : character(32) Ga_id : bigint Source : character(32) Medium : character(32) dwh_company_id : bigint =20 On top of which I apply a case when statement=E2=80=A6 =20 Now the weird thing is, using this query I notice a significant drop in = performance as the =E2=80=9Ccase when=E2=80=9D is getting bigger. If I = run the query as if, I get the following exec plain and execution time: Subquery Scan on wv (cost=3D6.00..29098.17 rows=3D100000 width=3D36) = (actual time=3D0.828..22476.917 rows=3D100000 loops=3D1)=20 Buffers: shared hit=3D3136=20 -> Limit (cost=3D6.00..11598.17 rows=3D100000 width=3D58) (actual = time=3D0.209..133.429 rows=3D100000 loops=3D1)=20 Buffers: shared hit=3D3136=20 -> Hash Join (cost=3D6.00..1069811.24 rows=3D9228690 = width=3D58) (actual time=3D0.208..119.297 rows=3D100000 loops=3D1)=20 Hash Cond: (wv_1.ga_id =3D ga.ga_id)=20 Buffers: shared hit=3D3136=20 -> Seq Scan on web_visits wv_1 (cost=3D0.00..877005.78 = rows=3D20587078 width=3D50) (actual time=3D0.004..18.412 rows=3D100000 = loops=3D1)=20 Buffers: shared hit=3D3133=20 -> Hash (cost=3D5.50..5.50 rows=3D40 width=3D12) = (actual time=3D0.184..0.184 rows=3D111 loops=3D1)=20 Buckets: 1024 Batches: 1 Memory Usage: 5kB=20 Buffers: shared hit=3D3=20 -> Hash Join (cost=3D1.88..5.50 rows=3D40 = width=3D12) (actual time=3D0.056..0.148 rows=3D111 loops=3D1)=20 Hash Cond: (ga.dwh_source_id =3D = s.dwh_source_id)=20 Buffers: shared hit=3D3=20 -> Seq Scan on google_analytics ga = (cost=3D0.00..2.89 rows=3D89 width=3D8) (actual time=3D0.007..0.028 = rows=3D111 loops=3D1)=20 Buffers: shared hit=3D2=20 -> Hash (cost=3D1.39..1.39 rows=3D39 = width=3D8) (actual time=3D0.042..0.042 rows=3D56 loops=3D1)=20 Buckets: 1024 Batches: 1 Memory = Usage: 3kB=20 Buffers: shared hit=3D1=20 -> Seq Scan on sources s = (cost=3D0.00..1.39 rows=3D39 width=3D8) (actual time=3D0.005..0.020 = rows=3D56 loops=3D1)=20 Buffers: shared hit=3D1=20 Planning time: 0.599 ms=20 Execution time: 22486.216 ms =20 Then try commenting out only one line in the case when and the query run = 10x faster : =20 Subquery Scan on wv (cost=3D6.00..28598.17 rows=3D100000 width=3D36) = (actual time=3D0.839..2460.002 rows=3D100000 loops=3D1)=20 Buffers: shared hit=3D3136=20 -> Limit (cost=3D6.00..11598.17 rows=3D100000 width=3D58) (actual = time=3D0.210..112.043 rows=3D100000 loops=3D1)=20 Buffers: shared hit=3D3136=20 -> Hash Join (cost=3D6.00..1069811.24 rows=3D9228690 = width=3D58) (actual time=3D0.209..99.513 rows=3D100000 loops=3D1)=20 Hash Cond: (wv_1.ga_id =3D ga.ga_id)=20 Buffers: shared hit=3D3136=20 -> Seq Scan on web_visits wv_1 (cost=3D0.00..877005.78 = rows=3D20587078 width=3D50) (actual time=3D0.004..14.048 rows=3D100000 = loops=3D1)=20 Buffers: shared hit=3D3133=20 -> Hash (cost=3D5.50..5.50 rows=3D40 width=3D12) = (actual time=3D0.184..0.184 rows=3D111 loops=3D1)=20 Buckets: 1024 Batches: 1 Memory Usage: 5kB=20 Buffers: shared hit=3D3=20 -> Hash Join (cost=3D1.88..5.50 rows=3D40 = width=3D12) (actual time=3D0.058..0.146 rows=3D111 loops=3D1)=20 Hash Cond: (ga.dwh_source_id =3D = s.dwh_source_id)=20 Buffers: shared hit=3D3=20 -> Seq Scan on google_analytics ga = (cost=3D0.00..2.89 rows=3D89 width=3D8) (actual time=3D0.007..0.025 = rows=3D111 loops=3D1)=20 Buffers: shared hit=3D2=20 -> Hash (cost=3D1.39..1.39 rows=3D39 = width=3D8) (actual time=3D0.042..0.042 rows=3D56 loops=3D1)=20 Buckets: 1024 Batches: 1 Memory = Usage: 3kB=20 Buffers: shared hit=3D1=20 -> Seq Scan on sources s = (cost=3D0.00..1.39 rows=3D39 width=3D8) (actual time=3D0.006..0.021 = rows=3D56 loops=3D1)=20 Buffers: shared hit=3D1=20 Planning time: 0.583 ms=20 Execution time: 2467.484 ms =20 Why this drop in performance for only one (in this simple example) = condition ? I do not really understand it. If I add more conditions to = the query (let say 1 or 2) it is also getting slower. And it=E2=80=99s = not a few ms, it is around 5 sec or so. (which is huge considering I = only take in my example 1/500 of my data with LIMIT. =20 Before we deviate from the problem I have (which is why the sudden drop = of performance) let me clarify a few things about this query : - The purpose is not to rewrite it, with a join or whatever, = the case when actually comes from a function which is auto-generated by = another app we have - My example is pretty simple and regex expressions could be = replaced by equals, the real case when query contains way more = complicated regex - This is subset of my CASE WHEN, it is much bigger, I cut it = at the =E2=80=9Cbottleneck=E2=80=9D point for this post. =20 Thanks a lot. =20 Best Regards, =20 Kevin =20 =20 ------=_NextPart_000_00B0_01D06BA4.8829BE30 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi Pavel,

 

Thanks for your = answer.

 

Yes sure, I could do that, but like I wrote = the purpose is not to find a way to rewrite it. But to understand why at = a certain point it is totally going off. =C2=A0I=E2=80=99m aware that = the longer my case when will be the longest the query will run. But 10x = slower for adding one condition, something feels wrong = here.

 

Plus, the case when is part of a function so = basically I use it this way :

SELECT col1, col2, get_channel(company_id, = source_id, =E2=80=A6)

FROM mytable;

 

Get_channel is coming from another app. And = even though I have, I need to assume that I don=E2=80=99t have the = control over this one and that I=E2=80=99m using it as = if.

 

This is only my debugging = query.

 

Best regards,

 

Kevin

 

From:<= /b> Pavel = Stehule [mailto:pavel.stehule@gmail.com]
Sent: Dienstag, 31. = M=C3=A4rz 2015 11:09
To: Kevin Viraud
Cc: = pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Weird = CASE WHEN behaviour causing query to be suddenly very = slow

 

Hi

long CASE can be = problem. Why you don't use a dictionary table and = join?

Regards

Pavel

 

2015-03-31 10:53 GMT+02:00 Kevin Viraud <kevin.viraud@rocket-internet.de>:

=

Hi,

 <= /o:p>

I have an issue with a rather large CASE WHEN and I cannot = figure out why  it is so slow...

 

First, here is my test query :

 

SELECT CASE WHEN dwh_company_id =3D = 1

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;       THEN CASE

 

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            = WHEN   wv.source ~ '^$' THEN 'Not = tracked'

          =       =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =      WHEN  wv.source ~ '^1$' THEN 'Not = tracked1'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^2$' THEN 'Not tracked2'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^3$' THEN 'Not tracked3'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^4$' THEN 'Not tracked4'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^5$' THEN 'Not tracked5'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^6$' THEN 'Not tracked6'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^7$' THEN 'Not tracked7'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^8$' THEN 'Not tracked8'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^9$' THEN 'Not tracked9'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^10$' THEN 'Not tracked10'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^11$' THEN 'Not tracked11'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^12$' THEN 'Not tracked12'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^13$' THEN 'Not tracked13'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^14$' THEN 'Not tracked14'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^15$' THEN 'Not tracked15'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^16$' THEN 'Not tracked16'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^17$' THEN 'Not tracked17'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^18$' THEN 'Not tracked18'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^19$' THEN 'Not tracked19'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^20$' THEN 'Not tracked20'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^21$' THEN 'Not tracked21'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^22$' THEN 'Not tracked22'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^23$' THEN 'Not tracked23'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^24$' THEN 'Not tracked24'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^25$' THEN 'Not tracked25'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^26$' THEN 'Not tracked26'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^27$' THEN 'Not tracked27'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^28$' THEN 'Not tracked28'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            = --WHEN  wv.source ~ '^29$' THEN 'Not = tracked29'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^30$' THEN 'Not tracked30'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^31$' THEN 'Not tracked31'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            WHEN  = wv.source ~ '^32$' THEN 'Not tracked32'

          =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =            = END

          =             &= nbsp;        = ELSE

          =             &= nbsp;        = 'Others'

          =             &= nbsp;        END as = channel

FROM (

          =             &= nbsp;        SELECT wv.id,

          =             &= nbsp;        =        wv.ga_id,

          =             &= nbsp;        =        split_part(wv.ga_source_medium,= ' / ', 1) as source,

          =             &= nbsp;        =        = ga.dwh_source_id,

          =             &= nbsp;        =        = s.dwh_company_id

          =             &= nbsp;        FROM = marketing.web_visits wv

          =             &= nbsp;        INNER JOIN = dwh_metadata.google_analytics ga ON ga.ga_id =3D = wv.ga_id

          =             &= nbsp;        INNER JOIN = dwh_manager.sources s ON ga.dwh_source_id = =3Ds.dwh_source_id

          =             &= nbsp;        --WHERE s.dwh_company_id = =3D 1

          =             &= nbsp;        LIMIT = 100000

          =       ) wv

 

 

This is a pretty simple case,  my subquery (or CTE = when using WITH statement) should return 5 fields with more or less this = structure :

Id : character(32)

Ga_id : bigint

Source : character(32)

Medium : character(32)

dwh_company_id : bigint

 

On top of which I apply a case when = statement=E2=80=A6

 

Now the weird thing is, using this query I notice a = significant drop in performance as the =E2=80=9Ccase when=E2=80=9D is = getting bigger. If I run the query as if, I get the following exec plain = and execution time:

Subquery Scan on wv  (cost=3D6.00..29098.17 = rows=3D100000 width=3D36) (actual time=3D0.828..22476.917 rows=3D100000 = loops=3D1)

   Buffers: shared hit=3D3136 =

   ->  Limit  = (cost=3D6.00..11598.17 rows=3D100000 width=3D58) (actual = time=3D0.209..133.429 rows=3D100000 loops=3D1)

         Buffer= s: shared hit=3D3136

         ->&= nbsp; Hash Join  (cost=3D6.00..1069811.24 rows=3D9228690 = width=3D58) (actual time=3D0.208..119.297 rows=3D100000 loops=3D1) =

          =      Hash Cond: (wv_1.ga_id =3D ga.ga_id) =

          =      Buffers: shared hit=3D3136 =

          =      ->  Seq Scan on web_visits = wv_1  (cost=3D0.00..877005.78 rows=3D20587078 width=3D50) (actual = time=3D0.004..18.412 rows=3D100000 loops=3D1)

          =            Buffers= : shared hit=3D3133

          =      ->  Hash  (cost=3D5.50..5.50 = rows=3D40 width=3D12) (actual time=3D0.184..0.184 rows=3D111 loops=3D1) =

          =            Buckets= : 1024  Batches: 1  Memory Usage: 5kB

          =            Buffers= : shared hit=3D3

          =            ->&n= bsp; Hash Join  (cost=3D1.88..5.50 rows=3D40 width=3D12) (actual = time=3D0.056..0.148 rows=3D111 loops=3D1)

          =             &= nbsp;    Hash Cond: (ga.dwh_source_id =3D = s.dwh_source_id)

          =             &= nbsp;    Buffers: shared hit=3D3 =

          =             &= nbsp;    ->  Seq Scan on google_analytics = ga  (cost=3D0.00..2.89 rows=3D89 width=3D8) (actual = time=3D0.007..0.028 rows=3D111 loops=3D1)

          =             &= nbsp;          Buffers:= shared hit=3D2

          =             &= nbsp;    ->  Hash  (cost=3D1.39..1.39 = rows=3D39 width=3D8) (actual time=3D0.042..0.042 rows=3D56 loops=3D1) =

          =             &= nbsp;          Buckets:= 1024  Batches: 1  Memory Usage: 3kB

          =             &= nbsp;          Buffers:= shared hit=3D1

          =             &= nbsp;          ->&nb= sp; Seq Scan on sources s  (cost=3D0.00..1.39 rows=3D39 width=3D8) = (actual time=3D0.005..0.020 rows=3D56 loops=3D1) =

          =             &= nbsp;           &n= bsp;    Buffers: shared hit=3D1 =

 Planning time: 0.599 ms

 Execution time: 22486.216 ms

 

Then try commenting out only one line in the case when and = the query run 10x faster :

 

Subquery Scan on wv  (cost=3D6.00..28598.17 = rows=3D100000 width=3D36) (actual time=3D0.839..2460.002 rows=3D100000 = loops=3D1)

   Buffers: shared hit=3D3136 =

   ->  Limit  = (cost=3D6.00..11598.17 rows=3D100000 width=3D58) (actual = time=3D0.210..112.043 rows=3D100000 loops=3D1)

         Buffer= s: shared hit=3D3136

         ->&= nbsp; Hash Join  (cost=3D6.00..1069811.24 rows=3D9228690 = width=3D58) (actual time=3D0.209..99.513 rows=3D100000 loops=3D1) =

          =      Hash Cond: (wv_1.ga_id =3D ga.ga_id) =

          =      Buffers: shared hit=3D3136 =

          =      ->  Seq Scan on web_visits = wv_1  (cost=3D0.00..877005.78 rows=3D20587078 width=3D50) (actual = time=3D0.004..14.048 rows=3D100000 loops=3D1)

          =            Buffers= : shared hit=3D3133

          =      ->  Hash  (cost=3D5.50..5.50 = rows=3D40 width=3D12) (actual time=3D0.184..0.184 rows=3D111 loops=3D1) =

          =            Buckets= : 1024  Batches: 1  Memory Usage: 5kB

          =            Buffers= : shared hit=3D3

          =            ->&n= bsp; Hash Join  (cost=3D1.88..5.50 rows=3D40 width=3D12) (actual = time=3D0.058..0.146 rows=3D111 loops=3D1)

          =             &= nbsp;    Hash Cond: (ga.dwh_source_id =3D = s.dwh_source_id)

          =             &= nbsp;    Buffers: shared hit=3D3 =

          =             &= nbsp;    ->  Seq Scan on google_analytics = ga  (cost=3D0.00..2.89 rows=3D89 width=3D8) (actual = time=3D0.007..0.025 rows=3D111 loops=3D1)

          =             &= nbsp;          Buffers:= shared hit=3D2

          =             &= nbsp;    ->  Hash  (cost=3D1.39..1.39 = rows=3D39 width=3D8) (actual time=3D0.042..0.042 rows=3D56 loops=3D1) =

          =             &= nbsp;          Buckets:= 1024  Batches: 1  Memory Usage: 3kB

          =             &= nbsp;          Buffers:= shared hit=3D1

          =             &= nbsp;          ->&nb= sp; Seq Scan on sources s  (cost=3D0.00..1.39 rows=3D39 width=3D8) = (actual time=3D0.006..0.021 rows=3D56 loops=3D1) =

          =             &= nbsp;           &n= bsp;    Buffers: shared hit=3D1 =

 Planning time: 0.583 ms

 Execution time: 2467.484 ms

 

Why this drop in performance for only one (in this simple = example) condition ? I do not really understand it. If I add more = conditions to the query (let say 1 or 2) it is also getting slower. And = it=E2=80=99s not a few ms, it is around 5 sec or so. (which is huge = considering I only take in my example 1/500 of my data with = LIMIT.

 

Before we deviate from the problem I have (which is why the = sudden drop of performance) let me clarify a few things about this query = :

-        = ;  The purpose is not to rewrite it, with = a join or whatever, the case when actually comes from a function which = is auto-generated by another app we have

-        = ;  My example is pretty simple and regex = expressions could be replaced by equals, the real case when query = contains way more complicated regex

-        = ;  This is subset of my CASE WHEN, it is = much bigger, I cut it at the =E2=80=9Cbottleneck=E2=80=9D point for this = post.

 

Thanks a lot.

 

Best Regards,

 

Kevin

 

 

------=_NextPart_000_00B0_01D06BA4.8829BE30--