pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave E Martin XXIII <postgresql-to.dave@dave.to>
To: pgsql-bugs@postgresql.org
Subject: Re: Index speeds up one row table (why)?
Date: Sat, 31 May 2003 16:56:56 -0600
Message-ID: <3ED93338.80806@dave.to> (raw)

Tom Lane Writes:

>Bruno Wolff III <bruno@wolff.to> writes:
>> It probably has one visible row in it. If it can changed a lot, there
>> may be lots of deleted tuples in a row. That would explain why an
>> index scan speeds things up.

>Right, every UPDATE on unique_ids generates a dead row, and a seqscan
>has no alternative but to wade through them all.  When a unique index is
>present, the indexscan code knows that after it's fetched one live tuple
...
>More-frequent vacuums would be a much more reliable solution,


The index I created wasn't unique (though it should have been), but 
perhaps much of the same reasoning still applies.

Also, I could have swore I tried a vacuum, and it didn't make a 
difference, although experimenting just now, it did. The data collection 
rate is considerably slower at the moment though, so perhaps last time 
the table simply quickly got "inefficient" very quickly again 
during/immediately after the vacuum (or I wasn't where I thought I was 
when I vacuumed). I'll have to experiment with this a bit more, when the 
data generation is high again.

(ok, experimented a bit more just now)
Hm, it appears that degredation occurs with the index as well, I guess 
at the time I created the index, it just initially did better because it 
got to skip all the already dead rows at creation time: but this is 
disturbing, I do a vacuum, and the access times are better, but still 
horrible:

explain analyze select next_id from bigint_unique_ids where 
table_name='CONNECTION_DATA';

 Index Scan using bigint_unique_ids__table_name on bigint_unique_ids  
(cost=0.00..8.01 rows=1 width=8) (actual time=13.77..844.14 rows=1 loops=1)
   Index Cond: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 844.36 msec
(3 rows)

vacuum; -- takes about 10 minutes
VACUUM

explain analyze select next_id from bigint_unique_ids where 
table_name='CONNECTION_DATA';
Index Scan using bigint_unique_ids__table_name on bigint_unique_ids  
(cost=0.00..84.01 rows=1 width=8) (actual time=0.17..99.94 rows=1 loops=1)
   Index Cond: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 100.09 msec

vacuum; --takes about 2 minutes
Index Scan using bigint_unique_ids__table_name on bigint_unique_ids  
(cost=0.00..179.01 rows=1 width=8) (actual time=0.45..219.05 rows=1 loops=1)
   Index Cond: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 219.20 msec

--ACK, worse, ran twice more, got 212.5 ms, and 394.39

vacuum bigint_unique_ids; -- try specific table only, takes about 5 seconds
Index Scan using bigint_unique_ids__table_name on bigint_unique_ids  
(cost=0.00..163.01 rows=1 width=8) (actual time=0.23..143.59 rows=1 loops=1)
   Index Cond: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 143.72 msec

vacuum full bigint_unique_ids; -- try full, takes about 3 seconds.
Seq Scan on bigint_unique_ids  (cost=0.00..1.02 rows=1 width=8) (actual 
time=0.10..0.10 rows=1 loops=1)
   Filter: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 0.25 msec

-- ah, much much much, better.

So apparently vacuum by itself isn't going to be sufficent, i'm going to 
need vacuum fulls? Or if I do vacuum's often enough (that should allow 
old rows to be overwritten?) will that do it? I'm a bit hazy on why 
vacuum isn't doing just as well as vacuum full, I thought the only 
difference was that full released space back to the operating system 
(and presumably defragments existing data, but for one row, this 
shouldn't matter?).

wait several minutes:

Seq Scan on bigint_unique_ids  (cost=0.00..39.01 rows=1 width=8) (actual 
time=2.97..2.98 rows=1 loops=1)
   Filter: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 3.13 msec
reindex index bigint_unique_ids__table_name;
REINDEX

Index Scan using bigint_unique_ids__table_name on bigint_unique_ids  
(cost=0.00..5.97 rows=1 width=8) (actual time=0.11..0.20 rows=1 loops=1)
   Index Cond: (table_name = 'CONNECTION_DATA'::text)
 Total runtime: 0.30 msec

It appears reindex has the same speed up effect. (and in this case made 
it switch back from seq_scan to index scan).

Let me throw in this too, if its helpful:

vacuum verbose bigint_unique_ids;
INFO:  --Relation public.bigint_unique_ids--
INFO:  Index bigint_unique_ids__table_name: Pages 29; Tuples 1: Deleted 
5354.
    CPU 0.01s/0.04u sec elapsed 0.05 sec.
INFO:  Removed 11348 tuples in 79 pages.
    CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  Pages 79: Changed 1, Empty 0; Tup 1: Vac 11348, Keep 0, UnUsed 0.
    Total CPU 0.03s/0.06u sec elapsed 0.14 sec.
INFO:  --Relation pg_toast.pg_toast_21592--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

vacuum full verbose bigint_unique_ids;
INFO:  --Relation public.bigint_unique_ids--
INFO:  Pages 79: Changed 1, reaped 79, Empty 0, New 0; Tup 1: Vac 297, 
Keep/VTL 0/0, UnUsed 11157, MinLen 52, MaxLen 52; Re-using: Free/Avail. 
Space 599716/22724; EndEmpty/Avail. Pages 76/3.
    CPU 0.01s/0.00u sec elapsed 0.01 sec.
INFO:  Index bigint_unique_ids__table_name: Pages 29; Tuples 1: Deleted 297.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Rel bigint_unique_ids: Pages: 79 --> 1; Tuple(s) moved: 1.
    CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  Index bigint_unique_ids__table_name: Pages 29; Tuples 1: Deleted 1.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_21592--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, 
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 
0/0; EndEmpty/Avail. Pages 0/0.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_21592_index: Pages 1; Tuples 0.
    CPU 0.00s/0.00u sec elapsed 0.01 sec.
VACUUM





view thread (8+ messages)  latest in thread

Message-ID: <3ED93338.80806@dave.to>
Permalink:  ../3ED93338.80806@dave.to/
Also on:    postgresql.org/message-id/3ED93338.80806@dave.to

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: postgresql-to.dave@dave.to, pgsql-bugs@postgresql.org
  Subject: Re: Index speeds up one row table (why)?
  In-Reply-To: <3ED93338.80806@dave.to>
  References: <3ED93338.80806@dave.to>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox