Re: index on ILIKE/LIKE - PostgreSQL 9.2

Поиск
Список
Период
Сортировка
От Lucas Possamai
Тема Re: index on ILIKE/LIKE - PostgreSQL 9.2
Дата
Msg-id CAE_gQfVCYRUeR3HvyqcnoJLmmq80ZhAzT6O=DEH=TE0Dy2LhtQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: index on ILIKE/LIKE - PostgreSQL 9.2  (Jeff Janes <jeff.janes@gmail.com>)
Ответы Re: index on ILIKE/LIKE - PostgreSQL 9.2  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-general
Дерево обсуждения
 
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Martijn Tonies \(Upscene Productions\)"
Дата:
Сообщение: Re: Beta testers for database development tool wanted
Следующее
От: Andreas Joseph Krogh
Дата:
Сообщение: Re: index on ILIKE/LIKE - PostgreSQL 9.2