From: jobapply <jobapply@nextmail.ru>
To: postgresql-performance@postgresql.org
Subject: Functional index is 5 times slower than the basic one
Date: Thu, 14 Jul 2005 02:35:10 +0400
Message-ID: <20050713223509.D720952C2C@svr1.postgresql.org> (raw)
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
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: Functional index is 5 times slower than the basic one
In-Reply-To: <20050713223509.D720952C2C@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