On Sat, Oct 12, 2013 at 11:08 AM, Scott Ribe
<scott_ribe@elevated-dev.com> wrote:
[skipped]
> select * from test where tz >= start and tz < end and colb like '%foobar%'
>
> Assume that the tz restriction is somewhat selective, say 1% of the table, and the colb restriction is extremely
selective,say less than 0.00001%.
[skipped]
> Here's a sample query plan:
>
> Index Scan using tz_cola on test (cost=0.56..355622.52 rows=23 width=106) (actual time=61.403..230.649 rows=4
loops=1)
> Index Cond: ((tz >= '2013-04-01 06:00:00-05'::timestamp with time zone) AND (tz <= '2013-04-30
06:00:00-05'::timestampwith time zone))
> Filter: ((colb)::text ~~ '%foobar%'::text)
> Rows Removed by Filter: 261725
> Total runtime: 230.689 ms
You need to create the pg_trgm extension
(http://www.postgresql.org/docs/9.3/static/pgtrgm.html) if you need
things like LIKE '%foobar%' to use indexes.
First create 2 separate indexes for your columns ... USING btree (tz)
and ... USING gin (colb gin_trgm_ops). And to EXPLAIN ANALYZE your
query. It could probably be the best option because BitmapAnd might be
very effective.
You could also try to create a multi-column index, but you will need
to create a btree_gin extension first
(http://www.postgresql.org/docs/9.3/static/btree-gin.html) to be able
to combine GIN and btree in one index ... gin (tz, colb gin_trgm_ops).
Then EXPLAIN ANALYZE the query and compare with the above.
Note, if you have intensive writes on the table you would probably
want to set FASTUPDATE to off on the GIN index, because it might lead
to unpredictable stalls
(http://www.postgresql.org/docs/9.3/static/gin-implementation.html#GIN-FAST-UPDATE).
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com