Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group