I have two separate columns in Postgres Database for Date and time I had Index in Date column and time column. As earlier I need to filter the data using date filter only Index worked perfectly. But In recent requirement I need to filter the data using both date and time filter. when I filter the data by combing date and time like concat(date_column, ' ', time_column) ::timestamp>='{var_date_time}' Date and time Index does not work . It takes time to fetch the data.

any way where I can create index by combining both Date and time columns?

CodePudding user response：

You need to create an index on exactly the expression you are going to use in your WHERE clause. You don't need to cast between a text and a timestamp, just add the two columns:

where date_column   time_column >= ...

Needs the following index:

create index on the_table ( (date_column   time_column) );

You might want to think about changing that into a single column in the long run.

CodePudding user response：

That is probably not a good way to store your data. It would be better as a single timestamptz column.

With your existing table and index, you can use a tuple comparison:

WHERE (date_column,time_column)>(date_constant,time_constant);

It is not clear to me if your existing structure is one multi-column index or two single-column indexes. Either way, it should use (one of) the indexes, but the multi-column would be better.