Why this index is never used?

Recently I’ve got a question from a coworker.

We have a table credit_reports, which have among others the following fields:

  • customer_id
  • inquiry_time
  • report_type

The table contains over 51,000,000 rows. There are several indexes for this table, among them:
idx1 – (customer_id, inquiry_time)
idx2(inquiry_time::date) – the timestamp converted to date

A coworker was wondering, why idx2 was never used, even when we use this transformation in the selection criteria, like:

SELECT * FROM credit_reports WHERE report_type=’type1′ AND inquiry_date::date > current_date-5

But if we omit the transformation:

SELECT * FROM credit_reports WHERE report_type=’type1′ AND inquiry_date > current_date-5

then idx1 will be used (although it is not on the first position in the index)

The answer is the following. idx2 is never going to be used (except of queries like SELECT min(inquiry_date::date) FROM credit_reports) because the first record was inserted 7 years ago, which means, there are about 2,500 distinct dates in the whole table, which makes at least 20,000 records per one index value…. no sane optimizer would choose this path!

As for the usage of idx1, it was used not exactly as an index, but rather was read sequentially, because it is faster than reading the whole table!

Advertisements

2 Comments

Filed under SQL

2 responses to “Why this index is never used?

  1. lily

    I understand about idx2, but I have a question: What is the maximum number of distinct values per index?

  2. There is no limit, but it takes too much time to extract records this way. each of these 20,000 will require a separate read.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s