Обсуждение: [HACKERS] too low cost of Bitmap index scan

Поиск
Список
Период
Сортировка

[HACKERS] too low cost of Bitmap index scan

От
Pavel Stehule
Дата:
Hi

I am trying to fix slow query on PostgreSQL 9.5.4.

The data are almost in RAM

I have a problem with too low cost slow Bitmap index scan on date column, that returns 300K rows.

Slow part
->  Bitmap Heap Scan on "Zasilka"  (cost=5097.39..5670.64 rows=1 width=12) (actual time=62.253..62.400 rows=3 loops=231)
     Recheck Cond: (("Dopravce" = "Dopravce_Ridic_1"."ID") AND ("StavDatum" > (now() - '10 days'::interval)))
     Filter: (("Stav" = 34) OR ("Stav" = 29) OR ("Stav" = 180) OR ("Stav" = 213) OR ("Stav" = 46) OR (("Produkt" = 33) AND ("Stav" = 179)) OR ((("ZpetnaZasilka" = '-1'::integer) OR ("PrimaZasilka" = '-1'::integer)) AND ("Stav" = 40)))
     Rows Removed by Filter: 154
     Heap Blocks: exact=22038
      ->  BitmapAnd  (cost=5097.39..5097.39 rows=144 width=0) (actual time=61.725..61.725 rows=0 loops=231)
                   ->  Bitmap Index Scan on "Zasilka_idx_Dopravce"  (cost=0.00..134.05 rows=7594 width=0) (actual time=1.030..1.030 rows=7608 loops=231)
                         Index Cond: ("Dopravce" = "Dopravce_Ridic_1"."ID")
                   ->  Bitmap Index Scan on "Zasilka_idx_StavDatum"  (cost=0.00..4963.34 rows=290487 width=0) (actual time=65.505..65.505 rows=354423 loops=210)
                         Index Cond: ("StavDatum" > (now() - '10 days'::interval))

When I disable bitmap scan, then the query is 6x time faster

   ->  Index Scan using "Dopravce_Ridic_idx_Kod" on "Dopravce_Ridic" "Dopravce_Ridic_1"  (cost=0.00..8.02 rows=1 width=4) (actual time=0.008..0.017 rows=1 loops=308)
         Index Cond: (("Kod")::text = ("Dopravce_Ridic"."Kod")::text)
         Filter: (substr(("Kod")::text, 1, 1) <> 'S'::text)
         Rows Removed by Filter: 0
   ->  Index Scan using "Zasilka_idx_Dopravce" on "Zasilka"  (cost=0.00..30489.04 rows=1 width=12) (actual time=15.651..17.187 rows=3 loops=231)
        Index Cond: ("Dopravce" = "Dopravce_Ridic_1"."ID")
       Filter: (("StavDatum" > (now() - '10 days'::interval)) AND (("Stav" = 34) OR ("Stav" = 29) OR ("Stav" = 180) OR ("Stav" = 213) OR ("Stav" = 46) OR (("Produkt" = 33) AND ("Stav" = 179)) OR ((("ZpetnaZasilka" = '-1'::integer) OR ("PrimaZasilka" = '-1'::integer)) AND ("Stav" = 40))))
        Rows Removed by Filter: 7596

I tested composite index ("Dopravce", "StavDatum"), but without success - planner still prefer bitmap index scan.

Table "Zasilka" is big with 15GB data

Regards

Pavel

Re: [HACKERS] too low cost of Bitmap index scan

От
Robert Haas
Дата:
On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> ->  Bitmap Heap Scan on "Zasilka"  (cost=5097.39..5670.64 rows=1 width=12)
> (actual time=62.253..62.400 rows=3 loops=231)
...
> When I disable bitmap scan, then the query is 6x time faster
....
>    ->  Index Scan using "Zasilka_idx_Dopravce" on "Zasilka"
> (cost=0.00..30489.04 rows=1 width=12) (actual time=15.651..17.187 rows=3
> loops=231)
>         Index Cond: ("Dopravce" = "Dopravce_Ridic_1"."ID")
>        Filter: (("StavDatum" > (now() - '10 days'::interval)) AND (("Stav" =
> 34) OR ("Stav" = 29) OR ("Stav" = 180) OR ("Stav" = 213) OR ("Stav" = 46) OR
> (("Produkt" = 33) AND ("Stav" = 179)) OR ((("ZpetnaZasilka" = '-1'::integer)
> OR ("PrimaZasilka" = '-1'::integer)) AND ("Stav" = 40))))
>         Rows Removed by Filter: 7596

I'm not sure, but my guess would be that the query planner isn't
getting a very accurate selectivity estimate for that giant filter
condition, and that's why the cost estimate is off.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] too low cost of Bitmap index scan

От
Pavel Stehule
Дата:


2016-12-19 23:28 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> ->  Bitmap Heap Scan on "Zasilka"  (cost=5097.39..5670.64 rows=1 width=12)
> (actual time=62.253..62.400 rows=3 loops=231)
...
> When I disable bitmap scan, then the query is 6x time faster
....
>    ->  Index Scan using "Zasilka_idx_Dopravce" on "Zasilka"
> (cost=0.00..30489.04 rows=1 width=12) (actual time=15.651..17.187 rows=3
> loops=231)
>         Index Cond: ("Dopravce" = "Dopravce_Ridic_1"."ID")
>        Filter: (("StavDatum" > (now() - '10 days'::interval)) AND (("Stav" =
> 34) OR ("Stav" = 29) OR ("Stav" = 180) OR ("Stav" = 213) OR ("Stav" = 46) OR
> (("Produkt" = 33) AND ("Stav" = 179)) OR ((("ZpetnaZasilka" = '-1'::integer)
> OR ("PrimaZasilka" = '-1'::integer)) AND ("Stav" = 40))))
>         Rows Removed by Filter: 7596

I'm not sure, but my guess would be that the query planner isn't
getting a very accurate selectivity estimate for that giant filter
condition, and that's why the cost estimate is off.

maybe operator cost is too high?

Regards

Pavel


--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] too low cost of Bitmap index scan

От
Robert Haas
Дата:
On Tue, Dec 20, 2016 at 2:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2016-12-19 23:28 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
>> On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>> > ->  Bitmap Heap Scan on "Zasilka"  (cost=5097.39..5670.64 rows=1
>> > width=12)
>> > (actual time=62.253..62.400 rows=3 loops=231)
>> ...
>> > When I disable bitmap scan, then the query is 6x time faster
>> ....
>> >    ->  Index Scan using "Zasilka_idx_Dopravce" on "Zasilka"
>> > (cost=0.00..30489.04 rows=1 width=12) (actual time=15.651..17.187 rows=3
>> > loops=231)
>> >         Index Cond: ("Dopravce" = "Dopravce_Ridic_1"."ID")
>> >        Filter: (("StavDatum" > (now() - '10 days'::interval)) AND
>> > (("Stav" =
>> > 34) OR ("Stav" = 29) OR ("Stav" = 180) OR ("Stav" = 213) OR ("Stav" =
>> > 46) OR
>> > (("Produkt" = 33) AND ("Stav" = 179)) OR ((("ZpetnaZasilka" =
>> > '-1'::integer)
>> > OR ("PrimaZasilka" = '-1'::integer)) AND ("Stav" = 40))))
>> >         Rows Removed by Filter: 7596
>>
>> I'm not sure, but my guess would be that the query planner isn't
>> getting a very accurate selectivity estimate for that giant filter
>> condition, and that's why the cost estimate is off.
>
> maybe operator cost is too high?

Hmm, seems like you'd be paying the operator cost either way.  No?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] too low cost of Bitmap index scan

От
Pavel Stehule
Дата:


2016-12-20 13:55 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
On Tue, Dec 20, 2016 at 2:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2016-12-19 23:28 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
>> On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>> > ->  Bitmap Heap Scan on "Zasilka"  (cost=5097.39..5670.64 rows=1
>> > width=12)
>> > (actual time=62.253..62.400 rows=3 loops=231)
>> ...
>> > When I disable bitmap scan, then the query is 6x time faster
>> ....
>> >    ->  Index Scan using "Zasilka_idx_Dopravce" on "Zasilka"
>> > (cost=0.00..30489.04 rows=1 width=12) (actual time=15.651..17.187 rows=3
>> > loops=231)
>> >         Index Cond: ("Dopravce" = "Dopravce_Ridic_1"."ID")
>> >        Filter: (("StavDatum" > (now() - '10 days'::interval)) AND
>> > (("Stav" =
>> > 34) OR ("Stav" = 29) OR ("Stav" = 180) OR ("Stav" = 213) OR ("Stav" =
>> > 46) OR
>> > (("Produkt" = 33) AND ("Stav" = 179)) OR ((("ZpetnaZasilka" =
>> > '-1'::integer)
>> > OR ("PrimaZasilka" = '-1'::integer)) AND ("Stav" = 40))))
>> >         Rows Removed by Filter: 7596
>>
>> I'm not sure, but my guess would be that the query planner isn't
>> getting a very accurate selectivity estimate for that giant filter
>> condition, and that's why the cost estimate is off.
>
> maybe operator cost is too high?

Hmm, seems like you'd be paying the operator cost either way.  No?

It looks so this cost is much more significant in index scan feature

Pavel 

 

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] too low cost of Bitmap index scan

От
Tom Lane
Дата:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I am trying to fix slow query on PostgreSQL 9.5.4.
> The data are almost in RAM

If it's all in RAM, you'd likely be well-served to lower random_page_cost.
It looks to me like the planner is estimating pretty accurately how many
heap fetches will be eliminated by using the extra index; where it's off
seems to be in the cost of those heap fetches relative to the index work.
        regards, tom lane

Re: [HACKERS] too low cost of Bitmap index scan

От
Pavel Stehule
Дата:


2016-12-21 0:01 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I am trying to fix slow query on PostgreSQL 9.5.4.
> The data are almost in RAM

If it's all in RAM, you'd likely be well-served to lower random_page_cost.
It looks to me like the planner is estimating pretty accurately how many
heap fetches will be eliminated by using the extra index; where it's off
seems to be in the cost of those heap fetches relative to the index work.

When I decrease random page cost, then the cost of bitmapscan was decreased too

https://explain.depesz.com/s/7CAJ .. random page cost 2
https://explain.depesz.com/s/iEBW .. random page cost 2, bitmapscan off
https://explain.depesz.com/s/W4zw .. random page cost 2
https://explain.depesz.com/s/Gar .. random page cost 1, bitmapscan off

I played with other costs, but without any success, the cost of bitmapscan is significantly cheaper then index scan.

Regards

Pavel


                        regards, tom lane