Bruno Wolff III <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
