Killing proper indexing: A neat idea

10.2014 / Category: / Tags: |

After being on the road to do PostgreSQL consulting for CYBERTEC for over a decade I noticed that there are a couple of ways to kill indexing entirely. One of the most favored ways is to apply functions or expressions on the column people want to filter on. It is a sure way to kill indexing entirely.

To help people facing this problem I decided to compile a small blog post addressing this issue. Maybe it is helpful to some of you out there.

Generating some sample data

To prove my point here is some sample data:

The script generates a list of around 10 million rows. A list lasting from January 2014 to May 1st, 2014 is generated (one entry per second). Note that generate_series will create a list of timestamps here. To find data quickly an index will be useful:

Checking indexing

Under normal conditions the index shows its value as soon as a proper WHERE clause is included in our statement:

However, in many cases people tend to transform the left side of the comparison when searching for data. In case of large data set this has disastrous consequences and performance will suffer badly. Here is what happens:

PostgreSQL does not transform the WHERE-clause into a range and therefore the index is not considered to be usable. To many PostgreSQL based applications this is more or less a death sentence when it comes to performance. A handful of inefficient sequential scans can ruin the performance of the entire application easily.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Alphatier Wolf
Alphatier Wolf
9 years ago

Nice Case description
The Problem is casting from timestamp to date type. so index is not used.

Thank you

Luan Huynh
Luan Huynh
7 years ago
Reply to  Alphatier Wolf

I agreed with you.
t = '2013-03-03' : Index Only Scan using idx_t on t_time
t::date = '2013-03-03': Seq Scan on t_time

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram