| 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-14 06:33:53 | 
| Message-ID: | 20050714063354.CFE0552D10@svr1.postgresql.org | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-performance | 
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..
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dennis | 2005-07-14 06:54:38 | Re: performance problems ... 100 cpu utilization | 
| Previous Message | Jeffrey W. Baker | 2005-07-14 06:33:41 | JFS fastest filesystem for PostgreSQL? |