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
Message-ID: <20050714063354.CFE0552D10@svr1.postgresql.org> (raw)
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..
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jobapply@nextmail.ru, postgresql-performance@postgresql.org
Subject: Re: Indexing Function called on VACUUM and sorting ?
In-Reply-To: <20050714063354.CFE0552D10@svr1.postgresql.org>
* 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