On Sat, Oct 12, 2013 at 11:08 AM, Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> 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.ru@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
