From: | "Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl> |
---|---|
To: | "Gregory Stark" <stark(at)enterprisedb(dot)com> |
Cc: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Planner: rows=1 after "similar to" where condition. |
Date: | 2008-02-25 12:13:37 |
Message-ID: | E4953B65D9E5054AA6C227B410C56AA9C3B1@exchange1.joris2k.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>-----Original Message-----
>From: Gregory Stark [mailto:stark(at)enterprisedb(dot)com]
>Sent: Monday, 25 February 2008 12:31
>To: Joris Dobbelsteen
>Cc: Scott Marlowe; pgsql-general(at)postgresql(dot)org
>Subject: Re: Planner: rows=1 after "similar to" where condition.
>
>"Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | hernan gonzalez | 2008-02-25 12:52:30 | Re: text and bytea |
Previous Message | Gregory Stark | 2008-02-25 11:31:25 | Re: Planner: rows=1 after "similar to" where condition. |