PostgreSQL 12.3 slow index scan chosen - Mailing list pgsql-performance

Hi PostgreSQL users,

I was looking at a slow query in our CMDB that using postgresql-12.3 as its
backend. I since I am using the pg_trgm extension elsewhere I decided to give
it a try. To my surprise, the query plan did not change. But when I disabled
the index scan I got the much, much faster scan using a bitmap index scan.
Any ideas about why that is being chosen? Here are the details:

shared_buffers = 12GB
work_mem = 128MB
maintenance_work_mem = 2GB
effective_io_concurrency = 200
max_worker_processes = 24
max_parallel_maintenance_workers = 4
max_parallel_workers_per_gather = 4
max_parallel_workers = 24 
random_page_cost = 1.1
seq_page_cost = 1.0
effective_cache_size = 36GB
default_statistics_target = 500
from_collapse_limit = 30
join_collapse_limit = 30

Slow version with index scan:

# explain analyze SELECT DISTINCT main.* FROM Articles main JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON
(ObjectCustomFieldValues_1.Disabled = '0' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id )  WHERE
(ObjectCustomFieldValues_1.LargeContentILIKE '%958575%' OR ObjectCustomFieldValues_1.Content ILIKE '%958575%') AND
(main.Disabled= '0')  ORDER BY main.SortOrder ASC, main.Name ASC;
 
                                                                                                 QUERY PLAN
                                                                                    
 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=892.65..892.68 rows=1 width=137) (actual time=21165.464..21165.464 rows=0 loops=1)
   ->  Sort  (cost=892.65..892.66 rows=1 width=137) (actual time=21165.462..21165.462 rows=0 loops=1)
         Sort Key: main.sortorder, main.name, main.id, main.summary, main.class, main.parent, main.uri, main.creator,
main.created,main.lastupdatedby, main.lastupdated
 
         Sort Method: quicksort  Memory: 25kB
         ->  Merge Join  (cost=0.71..892.64 rows=1 width=137) (actual time=21165.453..21165.453 rows=0 loops=1)
               Merge Cond: (main.id = objectcustomfieldvalues_1.objectid)
               ->  Index Scan using articles_pkey on articles main  (cost=0.14..9.08 rows=142 width=137) (actual
time=0.007..0.007rows=1 loops=1)
 
                     Filter: (disabled = '0'::smallint)
               ->  Index Scan using objectcustomfieldvalues3 on objectcustomfieldvalues objectcustomfieldvalues_1
(cost=0.56..807603.40rows=915 width=4) (actual time=21165.441..21165.441 rows=0 loops=1)
 
                     Filter: ((disabled = 0) AND ((largecontent ~~* '%958575%'::text) OR ((content)::text ~~*
'%958575%'::text)))
                     Rows Removed by Filter: 19030904
 Planning Time: 1.198 ms
 Execution Time: 21165.552 ms
(13 rows)

Time: 21167.239 ms (00:21.167)


Fast version with enable_indexscan = 0:

# explain analyze SELECT DISTINCT main.* FROM Articles main JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON
(ObjectCustomFieldValues_1.Disabled = '0' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id )  WHERE
(ObjectCustomFieldValues_1.LargeContentILIKE '%958575%' OR ObjectCustomFieldValues_1.Content ILIKE '%958575%') AND
(main.Disabled= '0')  ORDER BY main.SortOrder ASC, main.Name ASC;
 
                                                                                QUERY PLAN
                                                  
 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=1315.42..1315.45 rows=1 width=137) (actual time=0.306..0.306 rows=0 loops=1)
   ->  Sort  (cost=1315.42..1315.43 rows=1 width=137) (actual time=0.305..0.305 rows=0 loops=1)
         Sort Key: main.sortorder, main.name, main.id, main.summary, main.class, main.parent, main.uri, main.creator,
main.created,main.lastupdatedby, main.lastupdated
 
         Sort Method: quicksort  Memory: 25kB
         ->  Hash Join  (cost=52.89..1315.41 rows=1 width=137) (actual time=0.296..0.297 rows=0 loops=1)
               Hash Cond: (objectcustomfieldvalues_1.objectid = main.id)
               ->  Bitmap Heap Scan on objectcustomfieldvalues objectcustomfieldvalues_1  (cost=45.27..1305.40 rows=915
width=4)(actual time=0.213..0.213 rows=0 loops=1)
 
                     Recheck Cond: ((largecontent ~~* '%958575%'::text) OR ((content)::text ~~* '%958575%'::text))
                     Filter: (disabled = 0)
                     ->  BitmapOr  (cost=45.27..45.27 rows=1136 width=0) (actual time=0.210..0.210 rows=0 loops=1)
                           ->  Bitmap Index Scan on objectcustomfieldvalues_largecontent_trgm  (cost=0.00..15.40 rows=1
width=0)(actual time=0.041..0.041 rows=0 loops=1)
 
                                 Index Cond: (largecontent ~~* '%958575%'::text)
                           ->  Bitmap Index Scan on objectcustomfieldvalues_content_trgm  (cost=0.00..29.41 rows=1135
width=0)(actual time=0.168..0.168 rows=0 loops=1)
 
                                 Index Cond: ((content)::text ~~* '%958575%'::text)
               ->  Hash  (cost=5.84..5.84 rows=142 width=137) (actual time=0.079..0.079 rows=146 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 34kB
                     ->  Seq Scan on articles main  (cost=0.00..5.84 rows=142 width=137) (actual time=0.010..0.053
rows=146loops=1)
 
                           Filter: (disabled = '0'::smallint)
                           Rows Removed by Filter: 5
 Planning Time: 1.308 ms
 Execution Time: 0.356 ms
(21 rows)

Time: 2.113 ms

And the schema information:

# \d articles
                                          Table "public.articles"
    Column     |            Type             | Collation | Nullable |               Default                
---------------+-----------------------------+-----------+----------+--------------------------------------
 id            | integer                     |           | not null | nextval('articles_id_seq'::regclass)
 name          | character varying(255)      |           | not null | ''::character varying
 summary       | character varying(255)      |           | not null | ''::character varying
 sortorder     | integer                     |           | not null | 0
 class         | integer                     |           | not null | 0
 parent        | integer                     |           | not null | 0
 uri           | character varying(255)      |           |          | 
 creator       | integer                     |           | not null | 0
 created       | timestamp without time zone |           |          | 
 lastupdatedby | integer                     |           | not null | 0
 lastupdated   | timestamp without time zone |           |          | 
 disabled      | smallint                    |           | not null | 0
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)

# \d objectcustomfieldvalues
                                             Table "public.objectcustomfieldvalues"
     Column      |            Type             | Collation | Nullable |                         Default
       
 

-----------------+-----------------------------+-----------+----------+---------------------------------------------------------
 id              | integer                     |           | not null |
nextval('ticketcustomfieldvalues_id_s'::text::regclass)
 objectid        | integer                     |           | not null | 
 customfield     | integer                     |           | not null | 
 content         | character varying(255)      |           |          | 
 creator         | integer                     |           | not null | 0
 created         | timestamp without time zone |           |          | 
 lastupdatedby   | integer                     |           | not null | 0
 lastupdated     | timestamp without time zone |           |          | 
 objecttype      | character varying(255)      |           | not null |
 largecontent    | text                        |           |          | 
 contenttype     | character varying(80)       |           |          | 
 contentencoding | character varying(80)       |           |          | 
 sortorder       | integer                     |           | not null | 0
 disabled        | integer                     |           | not null | 0
Indexes:
    "ticketcustomfieldvalues_pkey" PRIMARY KEY, btree (id) CLUSTER
    "objectcustomfieldvalues1" btree (customfield, objecttype, objectid, content)
    "objectcustomfieldvalues2" btree (customfield, objecttype, objectid)
    "objectcustomfieldvalues3" btree (objectid, objecttype)
    "objectcustomfieldvalues4" btree (id) WHERE id IS NULL OR id = 0
    "objectcustomfieldvalues_content_trgm" gin (content gin_trgm_ops)
    "objectcustomfieldvalues_largecontent_trgm" gin (largecontent gin_trgm_ops)
    "ticketcustomfieldvalues1" btree (customfield, objectid, content)

Any suggestions would be appreciated.

Regards,
Ken