too low cost of Bitmap index scan
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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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: 7596I'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
Show quoted text
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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: 7596I'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
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.187rows=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: 7596I'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
Show quoted text
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 RAMIf 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
Show quoted text
regards, tom lane