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