Re: Strange (?) Index behavior?

On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Allen Landsidel <alandsidel(at)gmail(dot)com> writes:
> > Clustering is really unworkable in this situation.
>
> Nonetheless, please do it in your test scenario, so we can see if it has
> any effect or not.

It did not, not enough to measure anyway, which does strike me as
pretty odd.. Here's what I've got, after the cluster. Note that this
is also on a new filesystem, as I said, have been taking the chance to
experiment. The other two results were from a filesystem with 64KB
block size, 8KB fragment size. This one is 8KB and 8KB.

search=# explain analyze
search-# SELECT sname FROM testtable WHERE sname LIKE 'AA%';

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using sname_unique on "testtable" (cost=0.00..642138.83
rows=160399 width=20) (actual time=0.088..514438.470 rows=74612
loops=1)
Index Cond: ((sname >= 'AA'::text) AND (sname < 'AB'::text))
Filter: (sname ~~ 'AA%'::text)
Total runtime: 514818.837 ms
(4 rows)

Time: 514821.993 ms

>
> The speed you're getting works out to about 7.2 msec/row, which would be
> about right if every single row fetch caused a disk seek, which seems
> improbable unless the table is just huge compared to your available RAM.
>
> regards, tom lane

The CSV for the table is "huge" but not compared to RAM. The dump of
the database in native/binary format is ~1GB; the database currently
has only this table and the system stuff.

The time to fetch the first row was much faster with the cluster in
place, but after that, it's pretty much the same. 537s vs. 515s