> > Well, I've dropped index but INSERTs still take 70 sec and 
> > COPY just 1sec -:(((
> >
> 
> Did you run vacuum after dropping indexes ?
> Because DROP INDEX doesn't update relhasindex of pg_class,
> planner/executer may still look up pg_index.

Actually, I dropped and re-created table without indices...

Vadim

Sat, 27 May 2000 21:44:52 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Berkeley DB... 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
>>>> Well, I've dropped index but INSERTs still take 70 sec and 
>>>> COPY just 1sec -:(((

Mebbe so, but you can't blame it all on parse/plan overhead.

I did some experimentation on this with current sources, using a test
case of inserting 100,000 rows of 16 columns (the regression "tenk1"
table's contents repeated 10 times).  Each test was started with a
freshly created empty table.  The initial runs were done with all
postmaster options except -F defaulted.  All numbers are wall-clock time
in seconds; the "+" column is the time increase from the previous case:

load via COPY, fsync off:
0 indexes	24.45s
1 index		48.88s		+ 24.43
2 indexes	62.65s		+ 13.77
3 indexes	96.84s		+ 34.19
4 indexes	134.09s		+ 37.25

load via INSERTs, fsync off, one xact (begin/end around all inserts):
0 indexes	194.95s
1 index		247.21s		+ 52.26
2 indexes	269.69s		+ 22.48
3 indexes	307.33s		+ 37.64
4 indexes	352.72s		+ 45.39

load via INSERTs, fsync off, separate transaction for each insert:
0 indexes	236.53s
1 index		295.96s		+ 59.43
2 indexes	323.40s		+ 27.44
[ got bored before doing 3/4 index cases ... ]

load via INSERTs, fsync on, separate transactions:
0 indexes	5189.99s
[ don't want to know how long it will take with indexes :-( ]

So while the parse/plan overhead looks kinda bad next to a bare COPY,
it's not anything like a 70:1 penalty.  But an fsync per insert is
that bad and worse.

I then recompiled with -pg to learn more about where the time was going.
One of the useful places to look at is calls to FileSeek, since mdread,
mdwrite, and mdextend all call it.  To calibrate these numbers, the
table being created occupies 2326 pages and the first index is 343
pages.

Inserts (all in 1 xact), no indexes:
                0.00    0.00       1/109528      init_irels [648]
                0.00    0.00      85/109528      mdread [592]
                0.01    0.00    2327/109528      mdextend [474]
                0.01    0.00    2343/109528      mdwrite [517]
                0.23    0.00  104772/109528      _mdnblocks [251]
[250]    0.0    0.24    0.00  109528         FileSeek [250]
Inserts (1 xact), 1 index:
                0.00    0.00       1/321663      init_irels [649]
                0.00    0.00    2667/321663      mdextend [514]
                0.10    0.00   55478/321663      mdread [277]
                0.11    0.00   58096/321663      mdwrite [258]
                0.38    0.00  205421/321663      _mdnblocks [229]
[213]    0.1    0.60    0.00  321663         FileSeek [213]
COPY, no indexes:
                0.00    0.00       1/109527      init_irels [431]
                0.00    0.00      84/109527      mdread [404]
                0.00    0.00    2327/109527      mdextend [145]
                0.00    0.00    2343/109527      mdwrite [178]
                0.07    0.00  104772/109527      _mdnblocks [77]
[83]     0.0    0.07    0.00  109527         FileSeek [83]
COPY, 1 index:
                0.00    0.00       1/218549      init_irels [382]
                0.00    0.00    2667/218549      mdextend [220]
                0.07    0.00   53917/218549      mdread [106]
                0.08    0.00   56542/218549      mdwrite [99]
                0.14    0.00  105422/218549      _mdnblocks [120]
[90]     0.0    0.30    0.00  218549         FileSeek [90]

The extra _mdnblocks() calls for the inserts/1index case seem to be from
the pg_index scans in ExecOpenIndices (which is called 100000 times in
the inserts case but just once in the COPY case).  We know how to fix
that.  Otherwise the COPY and INSERT paths seem to be pretty similar as
far as actual I/O calls go.  The thing that jumps out here, however, is
that it takes upwards of 50000 page reads and writes to prepare a
343-page index.  Most of the write calls turn out to be from
BufferReplace, which is pretty conclusive evidence that the default
setting of -B 64 is not enough for this example; we need more buffers.

At -B 128, inserts/0index seems about the same, inserts/1index traffic is
                0.00    0.00       1/270331      init_irels [637]
                0.01    0.00    2667/270331      mdextend [510]
                0.06    0.00   29798/270331      mdread [354]
                0.06    0.00   32444/270331      mdwrite [277]
                0.40    0.00  205421/270331      _mdnblocks [229]
[223]    0.1    0.52    0.00  270331         FileSeek [223]
At -B 256, inserts/1index traffic is
                0.00    0.00       1/221849      init_irels [650]
                0.00    0.00    2667/221849      mdextend [480]
                0.01    0.00    5556/221849      mdread [513]
                0.01    0.00    8204/221849      mdwrite [460]
                0.37    0.00  205421/221849      _mdnblocks [233]
[240]    0.0    0.40    0.00  221849         FileSeek [240]
At -B 512, inserts/1index traffic is
                0.00    0.00       1/210788      init_irels [650]
                0.00    0.00      25/210788      mdread [676]
                0.00    0.00    2667/210788      mdextend [555]
                0.00    0.00    2674/210788      mdwrite [564]
                0.27    0.00  205421/210788      _mdnblocks [248]
[271]    0.0    0.28    0.00  210788         FileSeek [271]

So as long as the -B setting is large enough to avoid thrashing, there
shouldn't be much penalty to making an index.  I didn't have time to run
the COPY cases but I expect they'd be about the same.

Bottom line is that where I/O costs are concerned, the parse/plan
overhead for INSERTs is insignificant except for the known problem
of wanting to rescan pg_index for each INSERT.  The CPU overhead is
significant, at least if you're comparing no-fsync performance ...
but as I commented before, I doubt we can do a whole lot better in
that area for simple INSERTs.

			regards, tom lane

Sun, 28 May 2000 01:28:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Berkeley DB... 
> -----Original Message-----
> From: Mikheev, Vadim [mailto:vmikheev@SECTORBASE.COM]
> 
> > > Well, I've dropped index but INSERTs still take 70 sec and 
> > > COPY just 1sec -:(((
> > >
> > 
> > Did you run vacuum after dropping indexes ?
> > Because DROP INDEX doesn't update relhasindex of pg_class,
> > planner/executer may still look up pg_index.
> 
> Actually, I dropped and re-created table without indices...
>

Oops,aren't you testing in 6.5.3 ?
ExecOpenIndices() always refers to pg_index in 6.5.x.
Currently it doesn't refer to pg_index if relhasindex is
false. 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp 

Mon, 29 May 2000 13:51:42 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Berkeley DB... 