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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
