pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexing Function called on VACUUM and sorting ?
2+ messages / 1 participants
[nested] [flat]
* Indexing Function called on VACUUM and sorting ?
@ 2005-07-13 23:46 jobapply <jobapply@nextmail.ru>
0 siblings, 0 replies; 2+ messages in thread
From: jobapply @ 2005-07-13 23:46 UTC (permalink / raw)
To: postgresql-performance@postgresql.org
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..
^ permalink raw reply [nested|flat] 2+ messages in thread
* Indexing Function called on VACUUM and sorting ?
@ 2005-07-14 06:33 jobapply <jobapply@nextmail.ru>
0 siblings, 0 replies; 2+ messages in thread
From: jobapply @ 2005-07-14 06:33 UTC (permalink / raw)
To: postgresql-performance@postgresql.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..
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-07-14 06:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-13 23:46 Indexing Function called on VACUUM and sorting ? jobapply <jobapply@nextmail.ru>
2005-07-14 06:33 Indexing Function called on VACUUM and sorting ? jobapply <jobapply@nextmail.ru>
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