On 14/02/11 07:46, Gnanakumar wrote:
If you really need to match all those options, you can't use an index. A
substring-matching index would need to have multiple entries per
character per value (since it doesn't know what you will search for).
The index-size becomes unmanageable very quickly.
On 14/02/11 07:38, Artur Zając wrote:
I had almost the same problem.
To resolve it, I created my own text search parser (myftscfg) which divides
text in column into three letters parts, for example:
someem...@domain.com is divided to som, ome,mee,eem,ema,mai,ail,il@,
l@d,@do,dom,oma,mai,ain,in.,
> If you really need to match all those options, you can't use an index. A
> substring-matching index would need to have multiple entries per
> character per value (since it doesn't know what you will search for).
> The index-size becomes unmanageable very quickly.
> That's why I asked what you
On 14/02/11 07:28, Gnanakumar wrote:
Is that really what you are after? Or, did you just want to match:
us...@domain.com
us...@sub.domain.com
I understand that because I've (%) at the beginning and end, it's going to
match unrelated domains, etc., which as you said rightly, it is
wide-r
> Is that really what you are after? Or, did you just want to match:
>us...@domain.com
>us...@sub.domain.com
I understand that because I've (%) at the beginning and end, it's going to
match unrelated domains, etc., which as you said rightly, it is
wide-ranging. But my point here is that h
On 14/02/11 06:59, Gnanakumar wrote:
How can we boost performance of queries containing pattern matching
characters?
QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
As it is clear from the above query, email is matched "partially and
case-insensitively", which my application r
Hi,
How can we boost performance of queries containing pattern matching
characters? In my case, we're using a percent sign (%) that matches any
string of zero or more characters.
QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
EMAIL column is VARCHAR(256).
As it is clear from the
On Sun, Feb 13, 2011 at 10:49 PM, Josh Berkus wrote:
>
>> I fail to see how 1 through 3 can tell the planner the correlation
>> between two fields in two separate tables.
>
> CREATE CORRELATION_ESTIMATE ( table1.colA ) = ( table2.colB ) IS 0.3
>
> ... and then it fixes the correlation for *every*
> It would be easier to suggest what might be wrong if you included "EXPLAIN
> ANALYZE" output instead of just EXPLAIN.
> It's not obvious whether 8.3 or 8.4 is estimating things better.
Thanks for reply man
Turns out random_page_cost was set low in the 8.3.10 version - when I reset it
to 4(dfl
I found the difference.
Random_page_cost is 1 in the production 8.3.10, I guess weighting the decision
to use "index scan".
Thanks for the replies, gentlemen.
> If you diff the postgresql.conf files for both installs, what's different?
In the list below, 8.3.10 parameter value is in the clear, (
Mark Rostron wrote:
Was there any major optimizer change between 8.3.10 to 8.3.14?
I'm getting a difference in explain plans that I need to account for.
There were some major changes in terms of how hashing is used for some
types of query plans. And one of the database parameters,
defau
If you diff the postgresql.conf files for both installs, what's different?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Hi
My question is: Was there any major optimizer change between 8.3.10 to
8.3.14?
I'm getting a difference in explain plans that I need to account for.
We are running production pg8.3.10, and are considering upgrading to 8.4.x
(maybe 9.0), because we expected to benefit from some of th
For any database, anywhere, the answer is pretty much always RAID-10.
The only time you would do anything else is for odd special cases.
Cheers
Dave
On Sun, Feb 13, 2011 at 2:12 PM, sergey wrote:
> Hello,
>
> I got a disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It will
> be used
sergey wrote:
I got a disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It
will be used solely by PostgresQL database and I am trying to choose
the best RAID level for it.
..
Space is the least important factor. Even 1T will be enough.
Use RAID10, measure the speed of the whole arra
On Sun, Feb 13, 2011 at 3:54 PM, Scott Marlowe wrote:
> On Sun, Feb 13, 2011 at 1:12 PM, sergey wrote:
>> Hello,
>>
>> I got a disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It will
>> be used solely by PostgresQL database and I am trying to choose the best
>> RAID level for it.
>>
>
On Sun, Feb 13, 2011 at 1:12 PM, sergey wrote:
> Hello,
>
> I got a disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It will
> be used solely by PostgresQL database and I am trying to choose the best
> RAID level for it.
>
> The most priority is for read performance since we operate lar
On Sun, Feb 13, 2011 at 3:29 PM, Josh Berkus wrote:
> I've wordsmithed Chris's changes some, and then spun off a completely
> separate page for Hints discussion, since the NotToDo item was becoming
> too long.
>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>
> Please, N
Hello,
I got a disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It will
be used solely by PostgresQL database and I am trying to choose the best
RAID level for it.
The most priority is for read performance since we operate large data sets
(tables, indexes) and we do lots of searches/sc
I've wordsmithed Chris's changes some, and then spun off a completely
separate page for Hints discussion, since the NotToDo item was becoming
too long.
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
Please, NO!
This is exactly the kind of hint that I regard as a last resor
On Thu, Feb 10, 2011 at 9:25 AM, Chris Browne wrote:
> robertmh...@gmail.com (Robert Haas) writes:
>> On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
>> wrote:
>>> Well, I'm comfortable digging in my heels against doing *lame* hints
>>> just because "it's what all the other kids are doing," whic
21 matches
Mail list logo
