0

I have a query that filters on a boolean column that has index. But, the query takes ages to finish. When I don't use this filter, the query returns very quickly.

Here are the explain plans. The first one has the processed is true and takes ages to finish. The second one does not have it and returns immediately.

explain select count(*) from listen_events where (started_at >='2021-12-26' and started_at <'2021-12-27') and processed is true;
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=212405.62..212405.63 rows=1 width=8)
   ->  Bitmap Heap Scan on listen_events  (cost=187657.78..212390.09 rows=6213 width=0)
         Recheck Cond: ((started_at >= '2021-12-26 00:00:00'::timestamp without time zone) AND (started_at < '2021-12-27 00:00:00'::timestamp without time zone))
         Filter: (processed IS TRUE)
         ->  BitmapAnd  (cost=187657.78..187657.78 rows=6213 width=0)
               ->  Bitmap Index Scan on index_listen_events_on_started_at  (cost=0.00..17323.56 rows=813898 width=0)
                     Index Cond: ((started_at >= '2021-12-26 00:00:00'::timestamp without time zone) AND (started_at < '2021-12-27 00:00:00'::timestamp without time zone))
               ->  Bitmap Index Scan on listen_events_processed_idx  (cost=0.00..170330.87 rows=9125639 width=0)
                     Index Cond: (processed = true)
(9 rows)

=> explain select count(*) from listen_events where (started_at >='2021-12-26' and started_at <'2021-12-27');
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24549.13..24549.14 rows=1 width=8)
   ->  Gather  (cost=24548.92..24549.13 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23548.92..23548.93 rows=1 width=8)
               ->  Parallel Index Only Scan using index_listen_events_on_started_at on listen_events  (cost=0.58..22701.11 rows=339124 width=0)
                     Index Cond: ((started_at >= '2021-12-26 00:00:00'::timestamp without time zone) AND (started_at < '2021-12-27 00:00:00'::timestamp without time zone))
(6 rows)

Here is the table configuration:

Table "public.listen_events"
     Column     |            Type             | Collation | Nullable |                  Default                  | Storage  | Stats target | Description 
----------------+-----------------------------+-----------+----------+-------------------------------------------+----------+--------------+-------------
 id             | integer                     |           | not null | nextval('listen_events_id_seq'::regclass) | plain    |              | 
 event_type     | text                        |           |          |                                           | extended |              | 
 stream_type    | text                        |           |          |                                           | extended |              | 
 event_id       | text                        |           |          |                                           | extended |              | 
 broadcast_uid  | text                        |           |          |                                           | extended |              | 
 user_agent     | text                        |           |          |                                           | extended |              | 
 city           | text                        |           |          |                                           | extended |              | 
 country        | text                        |           |          |                                           | extended |              | 
 referrer       | text                        |           |          |                                           | extended |              | 
 country_code   | character varying(2)        |           |          |                                           | extended |              | 
 continent_code | character varying(2)        |           |          |                                           | extended |              | 
 user_id        | integer                     |           |          |                                           | plain    |              | 
 started_at     | timestamp without time zone |           |          |                                           | plain    |              | 
 created_at     | timestamp without time zone |           |          |                                           | plain    |              | 
 updated_at     | timestamp without time zone |           |          |                                           | plain    |              | 
 ip_address     | cidr                        |           |          |                                           | main     |              | 
 location       | point                       |           |          |                                           | plain    |              | 
 ended_at       | timestamp without time zone |           |          |                                           | plain    |              | 
 server_id      | text                        |           |          |                                           | extended |              | 
 channel_id     | integer                     |           |          |                                           | plain    |              | 
 id_bigint      | bigint                      |           |          |                                           | plain    |              | 
 processed      | boolean                     |           | not null | false                                     | plain    |              | 
Indexes:
    "listen_events_pkey" PRIMARY KEY, btree (id)
    "index_listen_events_event_id" btree (event_id)
    "index_listen_events_on_broadcast_uid" btree (broadcast_uid)
    "index_listen_events_on_started_at" btree (started_at)
    "index_listen_events_on_user_id" btree (user_id)
    "listen_events_processed_idx" btree (processed)
Options: autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=30000, autovacuum_vacuum_cost_delay=0, autovacuum_analyze_scale_factor=0, autovacuum_analyze_threshold=30000, toast.autovacuum_enabled=true

Currently, the table has 1.9 billion rows and the majority has processed = false.

Any clue why is this happening?

0

1 Answer 1

3

You don't show EXPLAIN (ANALYZE, BUFFERS) output, so I am reduced to guessing. Anyway there are two main differences:

  1. Since there is no single index for the query, PostgreSQL combines two indexes. That is some more work than scanning a single index.

  2. The main difference is that the fast query can use an index-only scan, while the slow query cannot.

I'd create a two-column index like this:

CREATE INDEX ON listen_events (processed, started_at);

If you only ever query for rows with processed IS TRUE, you could also create a smaller and faster index:

CREATE INDEX ON listen_events (started_at) WHERE processed IS TRUE;
4
  • Thank you. If I create the index on two columns, processed, started_at, do I still need the separate index on the processed column only? Jan 30, 2023 at 14:48
  • 1
    No, that should be unnecessary. Jan 30, 2023 at 15:08
  • 2
    @p.matsinopoulos: if you create the index to only support the query in your question, I would go for the filtered index.
    – user1822
    Jan 30, 2023 at 16:02
  • 2
    @p.matsinopoulos: With your given cardinalities I would certainly aim for the much smaller partial index. Since only true qualifies in a WHEREclause, you can generally simplify WHERE processed IS TRUE to just WHERE processed. Jan 30, 2023 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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