How to optimize the PostgreSQL query

Question

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');

You might want to store your numbers as numbers, and not as strings, so you don't have to continually cast them and filter out non-numeric values. — user229044
– user229044 ♦, Commented Sep 3, 2020 at 11:18
You should definitely store numbers as numbers. This 'N/A' and 'N?A' stuff is such nonsense; that's what nullable columns are for. Don't defer fixing the data into a proper format to the point of querying; do it before insertion. Unless you have some really good reason not to...? — underscore_d
– underscore_d, Commented Sep 3, 2020 at 11:22
Thanks @underscore_d for editing the term lakhs, and converting it to a 'normal' number. — Luuk
– Luuk, Commented Sep 3, 2020 at 11:25
@underscore_d thanks dear, but I can't change the schema as of now. I do not have permission to do so and many records were entered already. So that's the concern. — Vijay Kumar
– Vijay Kumar, Commented Sep 3, 2020 at 11:43
Can you at least edit to show the full schema of the table including column types and indexes? Else people will try to recommend adding indexes that you might already have. — underscore_d
– underscore_d, Commented Sep 3, 2020 at 11:45

Gordon Linoff · Accepted Answer · 2020-09-03 11:28:33Z

3

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.

edited Sep 3, 2020 at 11:28

answered Sep 3, 2020 at 11:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Laurenz Albe Over a year ago

Right, that's how NULL should be used.

Collectives™ on Stack Overflow

How to optimize the PostgreSQL query

1 Answer 1

1 Comment

Your Answer

Collectives™ on Stack Overflow

How to optimize the PostgreSQL query

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related