Re: like & optimization

On Sat, Oct 12, 2013 at 11:08 AM, Scott Ribe
<scott_ribe(at)elevated-dev(dot)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'::timestamp with 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(dot)ru(at)gmail(dot)com