On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row.

The following 2 indexes, I would think will act the exact same way, but they don't:


"cleanupAndAlerted001" btree (doneCleanup, alertedUser, doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;
"cleanupAndAlerted002" btree (doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

The query performed is of the form:

select distinct id, field1, field2, ... from my_table
doneCleanup = true AND
alertedUser = true AND
order by id asc, initialTimestamp asc limit 100 offset 50000;

Considering the conditions, I would expect identical behavior. Yet, the first index, even when the 2nd doesn't exist, does not get picked up.

Creating the 2nd index and doing the query again works, the new index gets used.

Am I assuming things I shouldn't be assuming? If so, what should I know about this behavior?

Edit: using postgres 11.2

  • 1
    Your query has a syntax error (comma after asc). Please fix that. Do you have an index on id? EXPLAIN (ANALYZE, BUFFERS) output would solve the mystery. – Laurenz Albe Feb 18 at 14:05
  • @LaurenzAlbe Indeed, forgot part of the order by part. There's a second part to the order by. Fixed. As for the buffers, I'll look into that. – KdgDev Feb 18 at 20:23
  • "ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list" – jjanes Feb 18 at 20:44
  • What version are you using? – jjanes Feb 18 at 20:44
  • @jjanes I'm using postgres 11.2 Also the query here is anonymized of course, it cannot be literally copied. – KdgDev Feb 19 at 19:23

Check for table and index sizes. Your first index uses 3 columns and its size is probably very big so optimizer chooses full table scan.

Index scan vs full table scan also depends on random_page_cost (default value 4).

If random_page_cost is set lower optimizer will chose index scans over full table scans.

  • Well, the old index is 25MB and the new one 3.4MB. Probably nothing there. – KdgDev Feb 19 at 20:12

Alright... let's have some test data.

doneCleanup BOOL NOT NULL, alertedUser BOOL NOT NULL, 
doneCleanupTimestamp TIMESTAMP NOT NULL, initialTimestamp TIMESTAMP NOT NULL);

INSERT INTO foo SELECT n, random()<0.9, random()<0.9, 
'2020-01-01'::TIMESTAMP+'1 SECOND'::INTERVAL*(n + random()*1000),
'2020-01-01'::TIMESTAMP+'1 SECOND'::INTERVAL*n
FROM generate_series(1,1000000) n;

CREATE INDEX foo_dad ON foo(doneCleanup, alertedUser, doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

EXPLAIN ANALYZE select distinct * FROM foo
doneCleanup = true AND
alertedUser = true AND
doneCleanupTimestamp<='2020-01-12 10:01:57'
order by id asc, initialTimestamp asc limit 100 offset 50000;
 Limit  (cost=10060.58..10078.70 rows=100 width=22) (actual time=27.482..28.697 rows=100 loops=1)
   ->  Unique  (cost=1000.54..145648.64 rows=798275 width=22) (actual time=7.410..27.133 rows=50100 loops=1)
         ->  Gather Merge  (cost=1000.54..135670.20 rows=798275 width=22) (actual time=7.409..18.259 rows=50100 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Incremental Sort  (cost=0.52..42529.43 rows=332615 width=22) (actual time=0.122..6.867 rows=17316 loops=3)
                     Sort Key: id, initialtimestamp, donecleanup, alerteduser, donecleanuptimestamp
                     Presorted Key: id
                     Full-sort Groups: 177  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
                     Worker 0:  Full-sort Groups: 710  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
                     Worker 1:  Full-sort Groups: 739  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
                     ->  Parallel Index Scan using foo_pkey on foo  (cost=0.42..27561.76 rows=332615 width=22) (actual time=0.015..4.461 rows=17345 loops=3)
                           Filter: (donecleanup AND alerteduser AND (donecleanuptimestamp <= '2020-01-12 10:01:57'::timestamp without time zone))
                           Rows Removed by Filter: 4038
 Planning Time: 0.375 ms
 Execution Time: 28.778 ms

With these parameters, this plan is fine. For test data generation, I had a hunch that it would be good that something called doneCleanup should be true for the majority of the rows, so I set it at 90% true with "random()<0.9". Also the condition on initialTimeStamp includes most of the table, which means the index is useless, and it is using the index on id to optimize the ORDER BY.

So, I restricted the condition on initialTimeStamp to

doneCleanupTimestamp<='2020-01-02 10:01:57'

...and I reproduced your problem. This index is used:

CREATE INDEX foo_d ON foo(doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

....but the other index including columns doneCleanup, alertedUser is not used. Let's try changing the proportion of bools set to true from 90% to 10%.

UPDATE foo SET doneCleanup=NOT doneCleanup, alertedUser=NOT alertedUser;

And now the other index including columns doneCleanup, alertedUser is used if the other is not available. If the smaller index including only doneCleanupTimestamp is there, then it is used instead, and it is faster, as expected.

Doing the update again to restore the previous distribution of bool value, then testing again while messing with random_page_cost, it appears it is possible to make it use the index if random_page_cost is low enough. But the index with the two extra columns is much, much slower.

The bool values in the index are all the same due to the WHERE clause restricting them both to true. So they are useless anyway, and the simpler index on the timestamp column should be used. But what would happen if I removed the WHERE instead? The answer is quite surprising: the index is used, and it is very fast.

So, there seems to be something in the postgres code that makes it not work very well when the bool first column(s) of an index are constant, AND this is specific to indices having WHERE clauses, since if I update all the bool columns to true, it still uses the index and it is fast...

  • Nice setup. I just checked the sizes. Table is 10GB(15-ish million rows), and the old index is actually only 25MB. The new index is 3.4MB. Not sure what's going on there. – KdgDev Feb 19 at 20:14
  • Turns out there's only about 90000 rows that match. – KdgDev Feb 19 at 20:15
  • It's pretty weird – bobflux Feb 19 at 20:34

The only explanation I have is that there are many rows that satisfy the condition on doneCleanupTimestamp, but few of them have doneCleanup and alertedUser equal to TRUE. Since PostgreSQL does not maintain cross-column statistics unless you request it with CREATE STATISTICS, it is not aware of the fact and decides that an index scan would not be efficient.

You could try

CREATE STATISTICS my_table_stats (dependencies)
   ON doneCleanup, alertedUser, doneCleanupTimestamp FROM my_table;

ANALYZE my_table;

and see if that makes a difference. But the second index is better anyway.

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.