I have a SQL optimization question.

Table Name: charts

Total Records: +250 million

Columns: timestamp(string), imported(boolean), ....


CREATE INDEX index_charts_on_timestamp ON public.charts 
USING btree ("timestamp") WHERE (imported = false)

CREATE UNIQUE INDEX charts_pkey ON public.charts USING btree (id)


SELECT  "charts"."id" FROM 
"charts" WHERE 
"charts"."imported" = FALSE AND 
"charts"."timestamp" = '2018-01-03' ORDER BY 
"charts"."id" ASC LIMIT 1000;


Limit  (cost=1000.59..237865.48 rows=1000 width=8)
 ->  Gather Merge  (cost=1000.59..33262989.10 rows=140426 width=8)
     Workers Planned: 2
 ->  Parallel Index Scan using charts_pkey on charts_pkey  (cost=0.57..33245780.43 rows=58511 width=8)
    Filter: ((NOT imported) AND (("timestamp")::text = '2018-01-03'::text))
(5 rows)

How can i make postgres to use index_charts_on_timestamp instead?

I cannot present EXPLAIN ANALYZE results, the command simply freezes without any results.

  • 1
    And why is timestamp's type text and not date or timestamp? – ypercubeᵀᴹ Jun 19 '19 at 11:07
  • @ypercubeᵀᴹ charts table stores raw data received from a csv file, we load the file into the table without any transformation – abhinavmsra Jun 20 '19 at 3:16

I suggest to improve the index to:

(timestamp, id) WHERE (imported = false)

so it fits the query better, for both the WHERE and ORDER BY clauses.

The plan will only need an index seek and a partial index scan (until it finds the required 1000 rows).

The current index does not hold the id values, so the whole index has to be read and then the id values retrieved from the table and sorted to get the first 1000.

If the rows matching the WHERE conditions are not many (less than 1000 or less than 10k), then the sort operation is not very expensive but the values still have to be read as they are not in the index. If the matching rows are a lot more (say 100K or more), then both fetching and sorting them will be much more expensive.
It might be cheaper in these cases to do a full table scan. The optimizer decided in your case to do something similar: a parallel index scan of the PK index. This avoids the need of sorting but it still has to read the rows from the table until it finds 1000 rows that match the WHERE condition.

Both cases however would have no problem with the suggested index above, as the table would not have to be read at all and no sorting needed as the index order matches the ORDER BY.

| improve this answer | |

PostgreSQL thinks that it will be faster by avoiding an explicit sort if it scans the table in sort order until it finds the first 1000 results that satisfy the condition.

This decision is based on the estimated selectivity of the WHERE conditions. So if the decision is wrong, that either means that the statistics are incorrect or that id and timestamp are correlated so that all the (many) rows with timestamp = '2018-01-03' have high ids, a dependency that PostgreSQL doesn't track.

You should proceed according to these possibilities:

  1. Gather fresh statistics with

    ANALYZE charts;

    and see if that improves the performance.

  2. If that doesn't work, re-phrase the ORDER BY condition so that PostgreSQL cannot use the primary key index:

    SELECT  charts.id
    FROM charts
    WHERE NOT charts.imported
      AND charts.timestamp = '2018-01-03'
    ORDER BY (charts.id + 0) ASC
    LIMIT 1000;
| improve this answer | |
  • I think it's also worth looking into this part of the plan: (("timestamp")::text = '2018-01-03'::text) – dezso Jun 19 '19 at 8:53
  • thanks @Laurenz Albe. I will try those steps – abhinavmsra Jun 19 '19 at 8:57
  • @denzo Any thing specific that I should be looking into? – abhinavmsra Jun 19 '19 at 8:57
  • @deszo That's what I though too initially. But as the question says it is a string, the data type is probably character varying, and the index can be used. – Laurenz Albe Jun 19 '19 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.