From: | "Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Planner: rows=1 after "similar to" where condition. |
Date: | 2008-02-24 22:35:41 |
Message-ID: | E4953B65D9E5054AA6C227B410C56AA9C3AF@exchange1.joris2k.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I seem to have some planner oddity, where it seems to completely
mispredict the output after a regex compare. I've seem it on other
occasions, where it completely screws up the join. You can note the
"rows=1" after the filter.
A similar sitution has occurred when doing a regex filter in a subquery,
which was subsequently predited as 1 row and triggered (oddly enough) a
sequencial scan. Doing the same using "equality" on the result to
substring(<text> from <regex>) seemed to work and produced a useful
plan, since it did a hash-join (as it should have).
Is this a known problem? Otherwise I think I should build a smaller test
case...
Using Postgresql 8.2.6 from Debian Etch-backports.
"Bitmap Heap Scan on log_syslog syslog (cost=13124.26..51855.25 rows=1
width=270)"
" Recheck Cond: (((program)::text = 'amavis'::text) AND
((facility)::text = 'mail'::text))"
" Filter: ***SOME VERY LONG SIMILAR TO REGEX****"
" -> BitmapAnd (cost=13124.26..13124.26 rows=18957 width=0)"
" -> Bitmap Index Scan on "IX_log_syslog_program"
(cost=0.00..2223.95 rows=92323 width=0)"
" Index Cond: ((program)::text = 'amavis'::text)"
" -> Bitmap Index Scan on "IX_log_syslog_facility"
(cost=0.00..10899.81 rows=463621 width=0)"
" Index Cond: ((facility)::text = 'mail'::text)"
- Joris
From | Date | Subject | |
---|---|---|---|
Next Message | James Robinson | 2008-02-24 22:38:49 | no-arg cluster and locks ... |
Previous Message | Kynn Jones | 2008-02-24 22:19:29 | "RETURNS SETOF" function question |