Ynt: Gained %20 performance after disabling bitmapscan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks for reply Tom,

AFAIK nothing changed with planner. Only max_parallel_*

[postgres@db-server ~]$ psql -c"show all" | grep parallel
 force_parallel_mode                 | off                                            | Forces use of parallel query facilities.
 max_parallel_workers                | 192                                            | Sets the maximum number of parallel workers than can be active at one time.
 max_parallel_workers_per_gather     | 96                                             | Sets the maximum number of parallel processes per executor node.
 min_parallel_index_scan_size        | 512kB                                          | Sets the minimum amount of index data for a parallel scan.
 min_parallel_table_scan_size        | 8MB                                            | Sets the minimum amount of table data for a parallel scan.
 parallel_setup_cost                 | 1000                                           | Sets the planner's estimate of the cost of starting up worker processes for parallel query.
 parallel_tuple_cost                 | 0.1                                            | Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend.

Queries written by developer team, I can only recommend them your suggestion.


Gönderen: Tom Lane <tgl@xxxxxxxxxxxxx>
Gönderildi: 19 Ekim 2018 Cuma 16:52:04
Kime: Yavuz Selim Sertoglu
Bilgi: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
Konu: Re: Gained %20 performance after disabling bitmapscan
 
Yavuz Selim Sertoglu <yavuzselim.sertoglu@xxxxxxxxxxxxxxxx> writes:
> I have a problem with my query. Query always using parallel bitmap heap scan.

Have you messed with the parallel cost parameters?  It seems a bit
surprising that this query wants to use parallelism at all.

>         Index Cond: (((mukellef_id)::text = '0123456789'::text) AND (kayit_tarihi >= '2018-01-01 00:00:00'::timestamp without time zone) AND (sube_no = '-13'::integer) AND ((defter)::text = 'sm'::text))

If that's your normal query pattern, then this isn't a very good
index design:

>     Column    |            Type             |  Definition
> --------------+-----------------------------+--------------
>  mukellef_id  | character varying(12)       | mukellef_id
>  kayit_tarihi | timestamp without time zone | kayit_tarihi
>  sube_no      | integer                     | sube_no
>  defter       | character varying(4)        | defter
>  id           | bigint                      | id

The column order should be mukellef_id, sube_no, defter, kayit_tarihi, id
so that the index entries you want are adjacent in the index.

Of course, if you have other queries using this index, you might need
to leave it as-is --- but this is the query you're complaining about...

                        regards, tom lane

YASAL UYARI:
Bu E-mail mesaji ve ekleri, isimleri yazili alicilar disindaki kisilere aciklanmamasi, dagitilmamasi ve iletilmemesi gereken kisiye ozel ve gizli bilgiler icerebilir. Mesajin muhatabi degilseniz lutfen gonderici ile irtibat kurunuz, mesaj ve eklerini siliniz.
E-mail sistemlerinin tasidigi guvenlik risklerinden dolayi, mesajlarin gizlilikleri ve butunlukleri bozulabilir, mesaj virus icerebilir. Bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi olasi zararlardan Sirketimiz sorumlu tutulamaz.
DISCLAIMER:
This email and its attachments may contain private and confidential information intended for the use of the addressee only, which should not be announced, copied or forwarded. If you are not the intended recipient, please contact the sender, delete the message and its attachments. Due to security risks of email systems, the confidentiality and integrity of the message may be damaged, the message may contain viruses. This message is scanned for known viruses and our Company will not be liable for possible system damages caused by the message.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux