Alban Hertroys wrote: >> Hi, >> >> It does! >> >> With your index alone: >> >> --------------------------- >> Bitmap Heap Scan on indexed_table (cost=815.17..10106.08 rows=33333 >> width=12) (actual time=7.796..236.722 rows=50116 loops=1) Recheck >> Cond: (data1 > this_is_a_long_transformation(data2)) >> -> Bitmap Index Scan on transform_index (cost=0.00..806.84 >> rows=33333 width=0) (actual time=7.665..7.665 rows=50116 loops=1) >> Total runtime: 459.380 ms --------------------------- >> >> That works just fine, but is there maybe a way of creating a slighly >> more "generic" index? If I change the ">" with a "<" in the query, >> index cannot of course be used. According to documentation, answer >> seems to be "no"... > > I have to say I'm a bit surprised this works, as the database still > needs to check all these rows for existence. Apparently the index is > sufficiently selective with your database tuning parameters. > > That said, if this works then a combined index on (data1, > this_is_a_long_transformation(data2)) will probably also work and > give you the flexibility you need. I have tried with a combined index: create index long_transformation_index on indexed_table (data1, this_is_a_long_transformation(data2)); Unfortunately, it does not work: ------------------------------- Seq Scan on indexed_table (cost=0.00..26791.00 rows=33333 width=12) (actual time=0.327..5805.199 rows=49959 loops=1) Filter: (data1 > this_is_a_long_transformation(data2)) Total runtime: 6340.772 ms ------------------------------- ----------------------------------------------------------------------- Philippe Lang Web : www.attiksystem.ch Attik System Email : philippe.lang@xxxxxxxxxxxxxx rte de la Fonderie 2 Phone : +41 26 422 13 75 1700 Fribourg Mobile : +41 79 351 49 94 Switzerland Fax : +41 26 422 13 76 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
