>-----Original Message----- >From: Gregory Stark [mailto:stark@xxxxxxxxxxxxxxxx] >Sent: Monday, 25 February 2008 12:31 >To: Joris Dobbelsteen >Cc: Scott Marlowe; pgsql-general@xxxxxxxxxxxxxx >Subject: Re: Planner: rows=1 after "similar to" where condition. > >"Joris Dobbelsteen" <Joris@xxxxxxxxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
