Re: Planner: rows=1 after "similar to" where condition.

On Sun, Feb 24, 2008 at 4:35 PM, Joris Dobbelsteen
<Joris(at)familiedobbelsteen(dot)nl> wrote:
> 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)"

It's not saying it will only get one row back for sure, it's saying it
thinks it will return one row. and depending on your query, it might.
What's the query, and what's the explain analyze of that query?