CODEWITHSUNDEEP
sqlpostgresql
Vijay Kumar
Vijay Kumar

Reputation: 1

How to optimize the PostgreSQL query

I currently have 1,100,000 rows in the table and it will increase over time. I am running this Postgres query on my database server. It's taking approx 5 sec to execute. How can I optimize it to make it execute faster?

Query:

    select sum(cast("total_value" as float)) as "total_value", sum(cast("fob_value" as float)) as "total_fob_value"
    from export
    where ("total_value" != 'N/A' and "total_value" != 'N?A') and
          ("fob_value" != 'N/A' and "fob_value" != 'N?A') and
          "product_desc" ilike '%pen%' and 
          ("shipping_date" between '2020-07-31T13:00:00.000Z' and '2020-08-28T09:58:04.451Z');

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

There is little that you can do for this query. Two possible indexes are a standard index on shipping_date or a GIN/GIST index for product_desc.

However, you can fix your data model. Do not store numeric values as strings. Invalid values can be stored using NULL. Also, do not use double quotes when defining column or table names. They just clutter queries.

With those changes, the query would simplify to:

select sum(total_value) as total_value, sum(fob_value) as total_fob_value
from export
where product_desc ilike '%pen%' and 
      shipping_date between '2020-07-31T13:00:00.000Z' and '2020-08-28T09:58:04.451Z';

This won't execute much faster, but it is much simpler to read and interpret.

Upvotes: 3

Related Questions