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

[Pavel Stehule]

2016-12-21 0:01 GMT+01:00 Tom Lane :

> Pavel Stehule  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
>

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

[Tom Lane]

Pavel Stehule  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Pavel Stehule]

2016-12-20 13:55 GMT+01:00 Robert Haas :

> On Tue, Dec 20, 2016 at 2:13 AM, Pavel Stehule 
> wrote:
> > 2016-12-19 23:28 GMT+01:00 Robert Haas :
> >> On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule  >
> >> 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

[Robert Haas]

On Tue, Dec 20, 2016 at 2:13 AM, Pavel Stehule  wrote:
> 2016-12-19 23:28 GMT+01:00 Robert Haas :
>> On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule 
>> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Pavel Stehule]

2016-12-19 23:28 GMT+01:00 Robert Haas :

> On Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule 
> 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 Sat, Dec 17, 2016 at 3:30 AM, Pavel Stehule  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[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