0

We are using Postgres 12 and We have a simple table like this:

> \d live_event_segmentation
                  Table "public.live_event_segmentation"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 login_id   | bigint                      |           | not null | 
 event_id   | text                        |           | not null | 
 expires_at | timestamp without time zone |           |          | 
Indexes:
    "live_event_segmentation_pkey" PRIMARY KEY, btree (login_id, event_id)
    "live_event_segmentations_event_id_idx" btree (event_id)

Size of the table is:

> \dt+ live_event_segmentation
                              List of relations
 Schema |          Name           | Type  |   Owner    |  Size  | Description 
--------+-------------------------+-------+------------+--------+-------------
 public | live_event_segmentation | table | liveevents | 171 MB | 
(1 row)

and the whole DB fits into RAM.

This table has this distribution of event_id:

> select event_id, count(*) from live_event_segmentation group by 1;
        event_id        | count  
------------------------+--------
 euro2020Test           |     67
 fevent20               | 164310
 summer2020Test         |      9
(3 rows)

And our app is executing this query a few times a second:

explain analyze select 1 as "one" where exists (select 1 as "one" from "public"."live_event_segmentation" where "public"."live_event_segmentation"."event_id" = 'summer2020' limit 1);
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=2.44..2.45 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Index Only Scan using live_event_segmentations_event_id_idx on live_event_segmentation  (cost=0.42..2.44 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)
           Index Cond: (event_id = 'summer2020'::text)
           Heap Fetches: 0
 Planning Time: 0.106 ms
 Execution Time: 0.040 ms
(8 rows)

When we run this query from psql it always uses an index only scan, but when our Java app runs this same query it often uses a sequential scan. We saw this from the logs by using auto_explain extension and setting log_min_duration_statement=20 (so that we can see query parameters which auto_explain doesn't display). The only difference between psql and our app is that the app uses a prepared statement, so when we tried it in psql sometimes it would actually use a sequential scan:

> prepare p1(text, int) AS select 1 as "one" where exists (select 1 as "one" from "public"."live_event_segmentation" where "public"."live_event_segmentation"."event_id" = $1 limit $2);
PREPARE
> explain analyze execute p1('summer2020', 1);
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.29..0.30 rows=1 width=4) (actual time=67.093..67.093 rows=0 loops=1)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..2396.72 rows=8129 width=4) (actual time=67.090..67.090 rows=0 loops=1)
           ->  Seq Scan on live_event_segmentation  (cost=0.00..23968.34 rows=81294 width=4) (actual time=67.087..67.087 rows=0 loops=1)
                 Filter: (event_id = $1)
                 Rows Removed by Filter: 163728
 Planning Time: 0.138 ms
 Execution Time: 67.171 ms
(9 rows)

What's important here is that the event summer2020 doesn't exist in the table. We noticed that only queries that are selecting event_id that doesn't exist sometimes use a sequential scan.

Table live_event_segmentation increases constantly with about 1 row per second, which is very slow. Autovacuum is working and the table is correctly analyzed. Also this happens constantly, we constantly have sequential scans on this table because of the query above, not int some strange spikes.

0
1

After 5 consecutive invocations of execute p1('fevent20', 1), it decides to stop coming up with custom plans as they don't seem to be doing any good and so not worth the planning time. And from then on it will use a generic plan. That generic plan assumes $1 will match about half or a third of the table (the average of the 2 or 3 values which do exist which it knows about) but can stop after the first match, and for that a sequential scan is better than an index scan.

Since PostgreSQL v12, you can set plan_cache_mode TO force_custom_plan to prevent it from doing this.

If you are before that version, maybe you can do something in your java app to force it not to use prepared queries, or to re-prepare the query each time. But if it is only executed a few times a second, taking 68ms for each is unfortunate but hardly desperate. Maybe you can just live with it until it does become more of a problem, or until your weird data distribution becomes more normal (assuming it ever does), or you upgrade to v12.

Also, doesn't your query give the same results as the simpler:?

prepare p1(text, int) AS select 1 as "one" from "public"."live_event_segmentation" where "public"."live_event_segmentation"."event_id" = $1 limit $2;

For some reason I don't understand, this query doesn't suffer from the generic plan switch.

3
  • You are describing what happens when PG searches for fevent20 and why it uses sequential scan, that part I understand, but I didn't ask that. I asked why does it use sequential scan for summer2020 that doesn't exist in the table, when in that case an index only scan is an order of magnitude better. – Strahinja Kustudic Jul 29 '20 at 8:04
  • I did answer the question you asked. Once it switches to a generic plan, it doesn't know you are asking for 'summer2020' rather than 'fevent20'. That is what it means to be a generic plan. – jjanes Jul 29 '20 at 15:48
  • You're right. Now I understand what you're saying. Thanks! – Strahinja Kustudic Jul 30 '20 at 21:16

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.