Possible infinite loop in query using bitmap scans
От | Casey Duncan |
---|---|
Тема | Possible infinite loop in query using bitmap scans |
Дата | |
Msg-id | c8347454067e35eef4f91645165af744@pandora.com обсуждение исходный текст |
Ответы |
Re: Possible infinite loop in query using bitmap scans
|
Список | pgsql-general |
I have this report query that runs daily on a table with several hundred million rows total using pg 8.1.3 on Debian Linux on hw with dual opteron processors: SELECT count(*) FROM webhits WHERE path LIKE '/radio/tuner_%.swf' AND status = 200 AND date_recorded >= '3/10/2006'::TIMESTAMP AND date_recorded < '3/11/2006'::TIMESTAMP; Here is the explain output: QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ---------------------------------- Aggregate (cost=794775.08..794775.09 rows=1 width=0) -> Bitmap Heap Scan on webhits (cost=315820.45..794771.74 rows=1337 width=0) Recheck Cond: ((date_recorded >= '2006-03-10 00:00:00'::timestamp without time zone) AND (date_recorded < '2006-03-11 00:00:00'::timestamp without time zone)) Filter: (((path)::text ~~ '/radio/tuner_%.swf'::text) AND (status = 200)) -> BitmapAnd (cost=315820.45..315820.45 rows=249152 width=0) -> Bitmap Index Scan on webhits_date_idx1 (cost=0.00..140407.45 rows=15379741 width=0) Index Cond: ((date_recorded >= '2006-03-10 00:00:00'::timestamp without time zone) AND (date_recorded < '2006-03-11 00:00:00'::timestamp without time zone)) -> Bitmap Index Scan on webhits_path_idx2 (cost=0.00..175412.76 rows=15343959 width=0) Index Cond: (((path)::text >= '/radio/tuner'::character varying) AND ((path)::text < '/radio/tunes'::character varying)) According to the planner it should take <15 minutes which is typical in practice. About half the times it runs, however, it never terminates (even after days) and just spins consuming 99+% of CPU with no disk activity. This query was never a problem in postgres versions < 8.1.2, however the data has grown substantially since that time. I notice it uses the recent in-memory bitmap feature, so I wondered if it was exposing a bug. If I restart the postmaster, the query will complete in the expected time. -Casey
В списке pgsql-general по дате отправления: