Functional index is 5 times slower than the basic one

From: "jobapply" <jobapply(at)nextmail(dot)ru>
To: <postgresql-performance(at)postgresql(dot)org>
Subject: Functional index is 5 times slower than the basic one
Date: 2005-07-13 22:35:10
Message-ID: 20050713223509.D720952C2C@svr1.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

VACUUM FULL ANALYZE is performed right before tests.
UPDATE test SET t = xpath_string(x, 'movie/rating'::text); is performed also
to make selects equal.
Xpath_string is IMMUTABLE.

Table "public.test"
Column | Type | Modifiers | Description
--------+------------------+-----------+-------------
i | integer | |
t | text | |
x | text | |
d | double precision | |
Indexes:
"floatind" btree (d)
"i_i" btree (i) CLUSTER
"t_ind" btree (t)
"t_x_ind" btree (t, xpath_string(x, 'data'::text))
"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))
Has OIDs: no

explain analyze select count(*) from (
select * from test order by xpath_string(x, 'movie/rating'::text)
limit 1000 offset 10
) a;



QUERY PLAN
Aggregate (cost=342.37..342.37 rows=1 width=0) (actual
time=403.580..403.584 rows=1 loops=1)
-> Subquery Scan a (cost=3.27..339.87 rows=1000 width=0) (actual
time=4.252..398.261 rows=1000 loops=1)
-> Limit (cost=3.27..329.87 rows=1000 width=969) (actual
time=4.242..389.557 rows=1000 loops=1)
-> Index Scan using x_iii on test (cost=0.00..3266.00 rows=10000
width=969) (actual time=0.488..381.049 rows=1010 loops=1)
Total runtime: 403.695 ms


explain analyze select count(*) from (
select * from test order by t limit 1000 offset 10
) a;


QUERY PLAN
Aggregate (cost=339.84..339.84 rows=1 width=0) (actual time=26.662..26.666
rows=1 loops=1)
-> Subquery Scan a (cost=3.24..337.34 rows=1000 width=0) (actual
time=0.228..22.416 rows=1000 loops=1)
-> Limit (cost=3.24..327.34 rows=1000 width=969) (actual
time=0.217..14.244 rows=1000 loops=1)
-> Index Scan using t_ind on test (cost=0.00..3241.00 rows=10000
width=969) (actual time=0.099..6.371 rows=1010 loops=1)
Total runtime: 26.749 ms

Browse pgsql-performance by date

  From Date Subject
Next Message Simon Riggs 2005-07-13 22:51:16 Re: Quad Opteron stuck in the mud
Previous Message Ian Westmacott 2005-07-13 21:46:10 Re: cost-based vacuum