Re: index on ILIKE/LIKE - PostgreSQL 9.2

> 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.