>-----Original Message-----
>From: Gregory Stark [mailto:stark@enterprisedb.com]
>Sent: Monday, 25 February 2008 12:31
>To: Joris Dobbelsteen
>Cc: Scott Marlowe; pgsql-general@postgresql.org
>Subject: Re: Planner: rows=1 after "similar to" where condition.
>
>"Joris Dobbelsteen" <Joris@familiedobbelsteen.nl> writes:
>
>> Should be:
>> PostGreSQL 8.2.5 on x86_64-pc-linux-gnu (GCC 4.1.2.20061115)
>(Debian 4.1.1-21).
>
>In this case that may matter. One of the changes in the 8.2.6
>update was:
>
>. Improve planner's handling of LIKE/regex estimation in non-C
>locales (Tom)
>
>I seem to recall this largely had to do with negated regexp
>matches but I might only be remembering part of it. I would
>start by taking all the fixes for known bugs before trying to
>diagnose a new one :)
It seems debian backports are not upgraded on my system. So I performed
the upgrade to:
"PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)"
And indeed, I should have upgraded it before to the latest version.
It still spits out the same EXPLAIN ANALYZE result.
"Bitmap Heap Scan on log_syslog syslog (cost=11168.32..16988.84 rows=1
width=221) (actual time=11145.729..30067.606 rows=212 loops=1)"
" Recheck Cond: (((program)::text = 'amavis'::text) AND
((facility)::text = 'mail'::text))"
" Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~
'***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed [A-Za-z0-9]+,
[][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>,
(Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+,
queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
" -> BitmapAnd (cost=11168.32..11168.32 rows=2095 width=0) (actual
time=608.771..608.771 rows=0 loops=1)"
" -> Bitmap Index Scan on "IX_log_syslog_program"
(cost=0.00..490.06 rows=19160 width=0) (actual time=94.982..94.982
rows=85238 loops=1)"
" Index Cond: ((program)::text = 'amavis'::text)"
" -> Bitmap Index Scan on "IX_log_syslog_facility"
(cost=0.00..10677.75 rows=426214 width=0) (actual time=504.960..504.960
rows=455084 loops=1)"
" Index Cond: ((facility)::text = 'mail'::text)"
"Total runtime: 30068.868 ms"
From what little I know statistics and how, in postgres, the filter
expressions are computed, the "rows=1" prediction seems still a bit odd.
It seems the fix did not effect this query.
Thanks for your support so far,
- Joris
