expain INSERT INTO - Mailing list pgsql-novice

From Luca Ferrari
Subject expain INSERT INTO
Date
Msg-id 200707291534.22510.fluca1978@infinito.it
Whole thread Raw
List 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

pgsql-novice by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: timestamp to date and time column migration
Next
From: Dale Seaburg
Date:
Subject: Not able to find a table