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?
The table is 9GB bigThe gin index is 400MB bigshared_buffers = 1536MBRAM = 8 GB
It would be interesting to see the output of explain (analyze, buffers) 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.672Total runtime: 24028.551 ms
There have been improvements in this area since 9.2, you should consider upgrading to at least 9.4.
pgsql-general by date:
Соглашаюсь с условиями обработки персональных данных