Subject: Re: [SQL] plan not correct?
To: Bert <biertie@gmail.com>
References: 
 <CAFCtE1mVr5FcO+GvCyhMAKjtZEcuDLjpQ4oQvdRcoVerCBgtDg@mail.gmail.com>
 <56F0079D.9060108@aklaver.com>
 <CAFCtE1=vdCDSFnM_Li_qmBa2cCcji6JJbt2ECizFAyUpoA1anQ@mail.gmail.com>
 <56F00CCF.9070702@aklaver.com>
 <CAFCtE1mLLUCwQXuJ-_UyAYDAT0pfaeWgnvcN2QCmVKjUOgWNqA@mail.gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56F01ACF.2080104@aklaver.com>
Date: Mon, 21 Mar 2016 09:01:19 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFCtE1mLLUCwQXuJ-_UyAYDAT0pfaeWgnvcN2QCmVKjUOgWNqA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

On 03/21/2016 08:29 AM, Bert wrote:
> That is easy to check.
>
> Let's do the same test again:
> # select count(1) from dlp.st_itemseat;
>   count
> -------
>      12
> (1 row)
>
> # select count(1) from loaddlp.st_itemseat_insert;
>   count
> -------
>      87      --> of which 12 are already in the dlp.st_itemseat table
> (1 row)
>
> # explain analyze <upsert query>*
>                                                                             QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------
>   Insert on st_itemseat  (cost=55.47..69.97 rows=150 width=228) (actual
> time=2.345..2.345 rows=0 loops=1)
>     CTE upsert
>       ->  Update on st_itemseat et  (cost=17.50..55.42 rows=2 width=240)
> (actual time=0.493..0.545 rows=12 loops=1)
>             ->  Hash Join  (cost=17.50..55.42 rows=2 width=240) (actual
> time=0.303..0.318 rows=12 loops=1)
>                   Hash Cond: ((et.tick_server_id =
> st_itemseat_insert_1.tick_server_id) AND (et.itemseat_id =
> st_itemseat_insert_1.itemseat_id))
>                   ->  Seq Scan on st_itemseat et  (cost=0.00..13.10
> rows=310 width=14) (actual time=0.025..0.028 rows=12 loops=1)
>                   ->  Hash  (cost=13.00..13.00 rows=300 width=234)
> (actual time=0.244..0.244 rows=87 loops=1)
>                         Buckets: 1024  Batches: 1  Memory Usage: 13kB
>                         ->  Seq Scan on st_itemseat_insert
> st_itemseat_insert_1  (cost=0.00..13.00 rows=300 width=234) (actual
> time=0.005..0.120 rows=87 loops=1)
>     ->  Seq Scan on st_itemseat_insert  (cost=0.04..14.54 rows=150
> width=228) (actual time=0.637..0.726 rows=75 loops=1)
>           Filter: (NOT (hashed SubPlan 2))
>           Rows Removed by Filter: 12
>           SubPlan 2
>             ->  CTE Scan on upsert  (cost=0.00..0.04 rows=2 width=8)
> (actual time=0.498..0.561 rows=12 loops=1)
>   Planning time: 1.122 ms
>   Execution time: 2.682 ms
>
> # <upsert query>*
> INSERT 0 0
>
> # select count(1) from dlp.st_itemseat;
>   count
> -------
>      87
> (1 row)
>
>
> * the upsert query can be found attached to the first mail, but the
> difference is that the 'where loadtabletime' is removed
>
> As you can see the in the update part of the explain the 'rows' nr is
> 12. Which is what is expected.
> But the rows on the insert are again 0, while it should be 75.

They are seen, including the 12 rows that are filtered out for updating:

" ->  Seq Scan on st_itemseat_insert  (cost=0.04..14.54 rows=150 
width=228) (actual time=0.637..0.726 rows=75 loops=1)
          Filter: (NOT (hashed SubPlan 2))
          Rows Removed by Filter: 12
          SubPlan 2
"

I do not know why that value is not propagated up to 'Insert on 
st_itemseat ...'.


>
> wkr,
> Bert
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


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