From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Daniel Kalchev" <daniel@digsys.bg>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: Re: again on index usage 
Date: Thu, 17 Jan 2002 04:55:46 +0900
Message-ID: <EKEJJICOHDIEMGPNIFIJKEOGGHAA.Inoue@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <200201161112.NAA07690@dcave.digsys.bg>
Importance: Normal

> -----Original Message-----
> From: Daniel Kalchev [mailto:daniel@digsys.bg]
> 
> Well, we already played around this by first creating new table 
> ordered by 
> random().

This seem to have little meaing if there's only one ipdate value
which satisfies the WHERE clause and the index order matches
the physical order of heap tuples.

I tried a very simple test case.
I have a table which has 100000 tuples and made a index on a
column whose value is always ''(i.e the same value).
Different from my expectation CREATE INDEX doesn't seem to
preserve the physical(input) order. However it seems sufficiently
ordered and probably we could expect the scan using the index
is as fast as sequential scan.

The result(distribution of corrsponding CTIDs) is as follows.

(3448, 17)
(3542, 26) -- (5172, 24)  consecutive 94539 tuples 
(3542, 25) -- (3448, 18)  consecutive 5460 tuples

regards,
Hiroshi Inoue