From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | too low cost of Bitmap index scan |
Date: | 2016-12-17 08:30:11 |
Message-ID: | CAFj8pRCzr00VZLh3eZghxp7Qw6+_Umaed13tQVZR7iKOXN+g5A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Sharma | 2016-12-17 10:23:34 | Re: Hang in pldebugger after git commit : 98a64d0 |
Previous Message | Bruce Momjian | 2016-12-17 04:42:55 | Re: Proposal for changes to recovery.conf API |