Postgresql – Index for range of timestamp with varchar fields

indexperformancepostgresqlpostgresql-9.5postgresql-performance

I am using postgress database (Version 9.5.4), I have a table structure like this,

create table test (
    id serial primary key,
    company_name varchar(255) not null,
    department varchar(255) not null,
    joined_date timestamp not null
);

Following are my exact queries (Only the below three not any other),

  1. Query 1

    SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25';

  2. Query 2

    SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx';

  3. Query 3

    SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'
  AND department = 'yyy';

I want to speed up my query execution time, I tried to add index some thing like this,

CREATE INDEX index_test ON test (joined_date, company_name, department);

After creating index, the query execution time doesn't have any improvement. On adding EXPLAIN keyword, I found the index is no longer used. But when adding equals condition instead of between keyword, index is being used. But my use case would be within date range and optional fields with equal condition. How to achieve my index use case with most efficient performance?

Best Answer

Index will be used if you remove cast to date (joined_date instead of joined_date::date) from WHERE clause (upper bound in BETWEEN should be changed accordingly). 

  select * 
  from test 
  where joined_date >= '2017-09-25' AND joined_date <'2017-10-26'  ;

Alternatively , you can create index as CREATE INDEX index_test2 ON test (cast (joined_date as date), company_name, department);

Proof

CREATE TABLE test (
    id           serial       PRIMARY KEY,
    company_name varchar(255) not null,
    department   varchar(255) not null,
    joined_date  timestamp    not null
);

INSERT INTO test(company_name, department,joined_date)
SELECT
  md5(x::text),
  CASE WHEN x%2 = 0 THEN 'foo' WHEN x%3 = 0 THEN 'bar' ELSE 'baz' END,
  now()
FROM generate_series(1,1e6)
  AS gs(x);

CREATE INDEX index_test
  ON test (joined_date, company_name, department);

VACUUM ANALYZE test;

Query 1

EXPLAIN ANALYZE SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25';  
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..8.45 rows=1 width=49) (actual time=0.069..0.069 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone))
 Planning time: 0.142 ms
 Execution time: 0.097 ms
(4 rows)

Query 2

EXPLAIN ANALYZE SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'; 
                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..8.45 rows=1 width=49) (actual time=0.016..0.016 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone) AND ((company_name)::text = 'xxx'::text))
 Planning time: 0.111 ms
 Execution time: 0.047 ms
(4 rows)

Query 3

SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'
  AND department = 'yyy';

                                                                                                                 QUERY PLAN                                                                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..6.20 rows=1 width=49) (actual time=0.017..0.017 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone) AND ((company_name)::text = 'xxx'::text) AND ((department)::text = 'yyy'::text))
 Planning time: 0.120 ms
 Execution time: 0.046 ms
(4 rows)
Related Question