Re: [HACKERS] too low cost of Bitmap index scan
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
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
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
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
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
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
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