plan not correct?

Поиск
Список
Период
Сортировка
От Bert
Тема plan not correct?
Дата
Msg-id CAFCtE1mVr5FcO+GvCyhMAKjtZEcuDLjpQ4oQvdRcoVerCBgtDg@mail.gmail.com
обсуждение исходный текст
Ответы Re: plan not correct?
Список pgsql-sql
Дерево обсуждения
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.

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
Вложения

В списке pgsql-sql по дате отправления: