Indexing Function called on VACUUM and sorting ?

From: "jobapply" <jobapply(at)nextmail(dot)ru>
To: <postgresql-performance(at)postgresql(dot)org>
Subject: Indexing Function called on VACUUM and sorting ?
Date: 2005-07-13 23:46:23
Message-ID: 20050713234621.302A952C5D@svr1.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


The question appeared because of strange issues with functional indexes.
It seems they are recalculated even where it is obviously not needed.

\d+ test:

i | integer | |
t | text | |
x | text | |
"i_i" btree (i)
"x_i" btree (xpath_string(x, 'data'::text))
"x_ii" btree (xpath_string(x, 'movie/characters/character'::text))
"x_iii" btree (xpath_string(x, 'movie/rating'::text))

1)
When I run
VACUUM FULL ANALYZE VERBOSE
OR
VACUUM ANALYZE

After text

INFO: analyzing "public.test"
INFO: "test": scanned 733 of 733 pages, containing 10000 live rows and 0
dead rows; 3000 rows in sample, 10000 estimated total rows

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..

Browse pgsql-performance by date

  From Date Subject
Next Message Marc McIntyre 2005-07-14 00:06:34 Slow Query
Previous Message Simon Riggs 2005-07-13 22:51:16 Re: Quad Opteron stuck in the mud