PostgreSQL – Create Multiple Field Indexes with Different Types

indexindex-tuningperformancepostgresql

I have the following query on an 18M records table (which grows daily by 5k):

SELECT * from marketing 
where start_time < '2017-02-19 08:00:00' 
AND NOT name LIKE '%MyWorld%' 
AND product = 'Product2'

I am using postgres 9.3.1

This is a report table that is being updated in an offline process, but I need to return the results from the report online, quickly.

I thought to use gin index, however, since my query filters 3 columns, I believe that I need a composite index

CREATE INDEX marketing_name_start_date_product_gin_trgm_idx ON tbl USING gist 
(name, start_date, product gin_trgm_ops);

However this seems like a waste for me to create a gin index for columns which don't use like in queries.

What do you suggest? What is the best approach?

Best Answer

This is going to be impossible for us to assist in with the information provided. However, rest assured 18M is not much. Baseline questions include,

  • Ignoring the LIKE condition, what is the selectivity that can be provided on start_time and product?
  • Do you ever select on start_time, and name, without product?
  • When you have a btree on (start_time, product), and a gin/gist tgrm index on name, how much times does the query take?
  • When you create the index described, how much time does the query take?

Take a look at the plans required by both. I would do these tests on a snap shot of the table so it's fair (or two snapshots with only the indexes mentioned)

CREATE TEMP TABLE foo
AS
  SELECT * FROM marketing
Related Question