Re: [SQL] plan not correct?

On 03/21/2016 07:54 AM, Bert wrote:

Ccing list
> Hello Ardian,
>
> The PostgreSQL version is 9.4.5
>
> The reason I have the 'returning' statement in the update section is
> because I only insert the data that has not been updated. I don't see
> why I would need to return anything in the insert section?

Well it was more about what you saw as the result of the UPDATE. It is
not clear to me whether that is 'UPDATE count' or the rows from RETURNING?

>
> On Mon, Mar 21, 2016 at 3:39 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 03/21/2016 07:03 AM, Bert wrote:
>
>         Dear all,
>
>         I am not sure if I am looking at a bug, or I am just doing
>         something wrong.
>         Anyhow, to me it seems that the plan for an upsert is wrong. (I
>         can not
>         find how many rows are inserted in the table)
>
>         Regard the following setup:
>         # select count(1) from dlp.st_itemseat;
>            count
>         -------
>                0
>         (1 row)
>
>         # select count(1) from loaddlp.st_itemseat_insert where
>         loadtabletime =
>         '2016-03-21 14:53:28.771467';
>            count
>         -------
>               12
>         (1 row)
>
>         # explain analyze <upsert query>*
>
>         QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------------------------
>            Insert on st_itemseat  (cost=26.14..41.39 rows=1 width=228)
>         (actual
>         time=1.282..1.282 rows=0 loops=1)
>              CTE upsert
>                ->  Update on st_itemseat et  (cost=0.15..26.11 rows=1
>         width=240)
>         (actual time=0.066..0.066 rows=0 loops=1)
>                      ->  Nested Loop  (cost=0.15..26.11 rows=1
>         width=240) (actual
>         time=0.061..0.061 rows=0 loops=1)
>                            ->  Seq Scan on st_itemseat_insert
>         st_itemseat_insert_1  (cost=0.00..13.75 rows=2 width=234) (actual
>         time=0.031..0.040 rows=12 loops=1)
>                                  Filter: (loadtabletime = '2016-03-21
>         14:53:28.771467'::timestamp without time zone)
>                                  Rows Removed by Filter: 75
>                            ->  Index Scan using pk_st_itemseat on
>         st_itemseat et
>         (cost=0.15..6.17 rows=1 width=14) (actual time=0.001..0.001
>         rows=0 loops=12)
>                                  Index Cond: ((tick_server_id =
>         st_itemseat_insert_1.tick_server_id) AND (itemseat_id =
>         st_itemseat_insert_1.itemseat_id))
>              ->  Seq Scan on st_itemseat_insert  (cost=0.02..15.27 rows=1
>         width=228) (actual time=0.175..0.201 rows=12 loops=1)
>                    Filter: ((loadtabletime = '2016-03-21
>         14:53:28.771467'::timestamp without time zone) AND (NOT (hashed
>         SubPlan 2)))
>                    Rows Removed by Filter: 75
>                    SubPlan 2
>                      ->  CTE Scan on upsert  (cost=0.00..0.02 rows=1
>         width=8)
>         (actual time=0.068..0.068 rows=0 loops=1)
>            Planning time: 1.022 ms
>            Execution time: 1.596 ms
>         (16 rows)
>
>
>         # <upsert query>*
>         INSERT 0 0
>
>         # select count(1) from dlp.st_itemseat;
>            count
>         -------
>               12
>         (1 row)
>
>         * the upsert query is added as an attachment to this mail.
>
>
>         In the query plan it seems that 0 rows are inserted; although 12
>         rows
>         are inserted when we compare the 2 counts.
>         When an update happens, the rows reported in the 'update'
>         statement are
>         correct.
>
>
>     Do you get a row count or the rows?
>
>     The reason I ask is that in the UPDATE section you have
>     '...returning ET.*', but not in the INSERT section.
>
>     Not sure if it matters in this case, but the Postgres version might
>     provide context.
>
>
>
>         Is this a bug? Or am I looking at the wrong part of the plan? I
>         would
>         like to check how many rows are actually inserted from the plan.
>
>         wkr,
>         Bert
>
>         --
>         Bert Desmet
>         0477/305361 <tel:0477%2F305361>
>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> Bert Desmet
> 0477/305361


--
Adrian Klaver
adrian.klaver@aklaver.com