Hi all,
a little question about explain when analyzing an INSERT query: explain
provides information about how the planner plan(s) and thus information about
how a query costs. But in the case of an insert of values that are included
in one index, explain does not provide information about the fact that the
insert will change the index structure and thus the query cost. This seems
correct to me because explain provides information about the plan, thus my
question is if exists a tool that provides cost information about insert
statements. The only way I found is to execute the query and see how much
does it costs....
A quick example:
coge=# \d gmmovart
Tabella "public.gmmovart"
Colonna | Tipo | Modificatori
-----------+----------------------+--------------
contatore | integer | not null
riga | integer | not null
data | date |
codice | character(16) |
unitam | character(2) |
qta | double precision |
numerob | integer |
rigab | integer |
lotto | character varying(8) |
Indici:
"gmmovart_pkey" PRIMARY KEY, btree (contatore, riga)
"gmmovart_lotto_idx" btree (lotto)
coge=# explain analyze insert into gmmovart(contatore,riga,lotto)
values(99,99,27999999);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.015..0.018 rows=1
loops=1)
Total runtime: 82.220 ms
(2 rows)
coge=# drop index gmmovart_lotto_idx;
coge=# explain analyze insert into gmmovart(contatore,riga,lotto)
values(98,98,27999999);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.013..0.015 rows=1
loops=1)
Total runtime: 0.113 ms
(2 rows)
As you can see the output of explain is the same in either the case with the
index or without it, thus how can I know how much an index is goingo to cost
me for tuple addition? Of course knowing it makes sense only for those table
where the queriy number is around the number of inserts.
Thanks,
Luca
