Re: index on ILIKE/LIKE - PostgreSQL 9.2

[Lucas Possamai <drum.lucas@xxxxxxxxx>]

 

How big is the table?  The gin index?  shared_buffers?  RAM?  What

kind of IO system do you have, and how many other things were going on
with it?

- Just a reminder that I'm not running these tests on my prod server.. I'm running on my test server. So the confs will be different

The table is 9GB big
The gin index is 400MB big
shared_buffers = 1536MB
RAM = 8 GB

I just wanted to understand why the GIN index is not working, but it works here: https://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

 

It would be interesting to see the output of explain (analyze,
buffers) with track_io_timing turned on.

explain analyze buffer with track_io_timing turned on:

Limit  (cost=93466.83..93466.83 rows=1 width=218) (actual time=24025.463..24025.478 rows=5 loops=1)
  Buffers: shared hit=8 read=42285
  I/O Timings: read=23599.672
  CTE ja_jobs
    ->  HashAggregate  (cost=93451.05..93455.90 rows=485 width=20) (actual time=23946.801..23967.660 rows=16320 loops=1)
          Buffers: shared hit=3 read=42285
          I/O Timings: read=23599.672
          ->  Bitmap Heap Scan on "ja_jobs"  (cost=877.70..93374.92 rows=30453 width=20) (actual time=161.372..23835.632 rows=48472 loops=1)
                Recheck Cond: (("clientid" = 14635) AND ("time_job" > 1436731799))
                Buffers: shared hit=3 read=42285
                I/O Timings: read=23599.672
                ->  Bitmap Index Scan on "ix_jobs_client_times"  (cost=0.00..870.09 rows=30453 width=0) (actual time=133.920..133.920 rows=48472 loops=1)
                      Index Cond: (("clientid" = 14635) AND ("time_job" > 1436731799))
                      Buffers: shared hit=3 read=244
                      I/O Timings: read=120.137
  ->  Sort  (cost=10.92..10.93 rows=1 width=218) (actual time=24025.457..24025.462 rows=5 loops=1)
        Sort Key: "ja_jobs"."title"
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=8 read=42285
        I/O Timings: read=23599.672
        ->  CTE Scan on "ja_jobs"  (cost=0.00..10.91 rows=1 width=218) (actual time=23977.095..24025.325 rows=5 loops=1)
              Filter: (("title")::"text" ~~* '%To Electrical%'::"text")
              Rows Removed by Filter: 16315
              Buffers: shared hit=3 read=42285
              I/O Timings: read=23599.672
Total runtime: 24028.551 ms

 

There have been improvements in this area since 9.2, you should
consider upgrading to at least 9.4.

 

Yep I know. The upgrade will happen, but I don't know when.