Re: plan not correct? - Mailing list pgsql-sql

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
>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com