expain INSERT INTO

From: Luca Ferrari <fluca1978(at)infinito(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: expain INSERT INTO
Date: 2007-07-29 13:34:22
Message-ID: 200707291534.22510.fluca1978@infinito.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Browse pgsql-novice by date

  From Date Subject
Next Message Bill Totman 2007-07-29 18:39:13 timestamp to date and time column migration
Previous Message Luca Ferrari 2007-07-29 13:25:42 doubt about datum