From: "jobapply" <jobapply@nextmail.ru>
To: <postgresql-performance@postgresql.org>
Subject: Indexing Function called on VACUUM and sorting ?
Date: Thu, 14 Jul 2005 10:33:53 +0400
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcWIBRPH0sxvB4uyS7CMZ6QrhZOEzw==
Message-Id: <20050714063354.CFE0552D10@svr1.postgresql.org>
ReSent-Date: Thu, 14 Jul 2005 17:57:59 -0300 (ADT)
Resent-From: "Marc G. Fournier" <scrappy@hub.org>
Resent-To: pgsql-performance@postgresql.org
ReSent-Subject: Indexing Function called on VACUUM and sorting ?
ReSent-Message-ID: <20050714175759.A66818@ganymede.hub.org>

It seems functional indexes are recalculated even where it is obviously not
needed.

\d+ test:

 i              | integer |                          |
 t              | text    |                          |
 x              | text    |                          |
    "i_i" btree (i)
    "x_iii" btree (xpath_string(x, 'movie/rating'::text))


1) 
When I run
VACUUM FULL ANALYZE VERBOSE 
OR
VACUUM ANALYZE
a lot of xpath_string calls occur. 
Does VACUUM rebuild indexes ? What for to recalculate that all?
It makes VACUUMing very slow.

Simple VACUUM call does not lead to such function calls.

2)
When I do 
select * from test order by xpath_string(x, 'movie/rating'::text) limit 1000
offset 10;

Planner uses index x_iii (as it should, ok here): 	Limit -> Index scan.
But many of calls to xpath_string occur in execution time. 
Why ? Index is calculated already and everything is so immutable..

Please answer if you have any ideas.. Functional indexes seemed so great
first, but now I uncover weird issues I can't understand..