1

I'm trying to optimize a slow query on PostgreSQL 15. The query is quite complex with many JOINs, a UNION and subqueries but 99% of execution time is in the innermost node that seq scan a 700K records table with a LIKE condition. Total exec time is ~15 sec.

Extracting the table and the WHERE condition from the query:

SELECT * FROM orders WHERE tag ILIKE '%EJ080EJ%';

Gather  (cost=1000.00..18961.33 rows=27 width=454) (actual time=60.552..69.402 rows=0 loops=1)
Workers Planned: 3
Workers Launched: 3
->  Parallel Seq Scan on orders  (cost=0.00..17958.63 rows=9 width=454) (actual time=47.991..47.992 rows=0 loops=4)
      Filter: ((tag)::text ~~* '%EJ080EJ%'::text)
      Rows Removed by Filter: 74044
Planning Time: 0.732 ms
Execution Time: 69.439 ms

So I added an index using GIN and pg_trgm extension that in my experience is useful with LIKE conditions:

CREATE INDEX tmp_dba2 ON orders USING GIN (tag gin_trgm_ops);

Bitmap Heap Scan on orders(cost=18.91..48.84 rows=27 width=454) (actual time=0.061..0.062 rows=0 loops=1)
  Recheck Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
  ->  Bitmap Index Scan on tmp_dba2  (cost=0.00..18.90 rows=27 width=0) (actual time=0.060..0.060 rows=0 loops=1)
        Index Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
Planning Time: 0.947 ms
Execution Time: 0.090 ms

So far so good. Anyway, executing the whole query the index is used but execution time doesn't change so much:

->  Bitmap Heap Scan on orders  (cost=19.22..94.62 rows=68 width=225) (actual time=13495.733..13495.740 rows=2 loops=1)
      Recheck Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
      Heap Blocks: exact=2
      Buffers: shared hit=21
      ->  Bitmap Index Scan on tmp_dba2  (cost=0.00..19.21 rows=68 width=0) (actual time=0.087..0.087 rows=2 loops=1)
            Index Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
            Buffers: shared hit=19

In my (poor) understanding of Postgres internals this means that the index scan retrieve a bitmap of the heap pages that fulfill the condition (2 records) and then the heap scan read the pages to retrive the data (2 records).

I can't understand why reading 2 records is taking 13 seconds, just like a seq scan, so adding the index appears useless.

Can anyone help me understanding this behaviour?

4
  • Does it get better when you VACUUM the table? Dec 14, 2023 at 12:59
  • It doesn't help, it's a production database regularly vacuumed and mantained. What bothers me is that a simple SELECT on the table with the same WHERE clause and the index is very fast. When surrounded by the rest of the query (20 left joins and some subqueries) it will slow down. Even incrementing work_mem doesn't do anything.
    – St.Mauro
    Dec 14, 2023 at 14:15
  • How long are those tags, and what data type are they? What is your collation (SELECT datcollate FROM pg_database WHERE datname = current_database();)?Try with the C collation. Try experimenting with LIKE instead of ILIKE. Dec 14, 2023 at 14:34
  • Thanks for your help. They're varchar(10), usually filled with 8 chars, NULL not allowed. Database collation is it_IT.UTF8. Tried experimenting with LIKE/ILIKE and "C" collation to no avail so far.
    – St.Mauro
    Dec 14, 2023 at 15:07

1 Answer 1

1

Probably what is happening is that this one part of the query is unfairly getting the blame for all the time taken up by JIT activity. Turn JIT off, as it probably isn't helping you anyway.

I would be more confident in this diagnosis if I could see the whole plan, not just a few rows extracted out of it. And if it is not JIT, then we would need to see the whole plan anyway to come up with alternative explanations.

4
  • Got it! With jit = off execuction time become 6ms from 15s. This was noticeable in the plan but I missed it:
    – St.Mauro
    Dec 15, 2023 at 8:26
  • I will check the configuration to understand if there is any optimization to be done as I'm hesitant to disable jit for the entire cluster. I can post the link to the plan before and after disabling jit if useful
    – St.Mauro
    Dec 15, 2023 at 8:33
  • I'd just turn it off. In my opinion, turning jit on by default was the biggest mistake the project has made in my memory. They are usually much more conservative about setting the defaults for new features like that. It harms far more people than it helps, and the size of the harm is far greater than the size of the help when it does help. It won't help to examine more execution plans (unless the 6ms is still too slow which doesn't seem likely).
    – jjanes
    Dec 15, 2023 at 18:06
  • I agree, in the end I turned it off. Overall statistics didn't show any loss in performance, I even noticed some gain here and there. This hit us unexpectedly, we've just migrated from v.11 to 15 without noticing that jit was enabled, so I agree with you that it was a mistake to make it enabled by default.
    – St.Mauro
    Dec 18, 2023 at 8:43

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.