Scott,
You may also end up setting higher statistics targets on some other columns
to fix other issues. You may want to set the value in the configuration
file higher than the default 10 -- I'd recommend starting with 40 and
re-analyzing the tables.
Thank you.
I set rid.toode statistics targ
> I used 1000 since doc wrote that max value is 1000
> Rid table contains 3.5millions rows, will increase 1 millions of rows per
> year and is updated frequently, mostly by adding.
> Is it OK to leave
> SET STATISTICS 1000;
> setting for this table this column or should I try to decrease it ?
Andrus wrote:
> Richard,
>
>>> Results are provided in bottom of the message to which you replied.
>>
>> No - the explains there were contrasting a date test BETWEEN versus =.
>
> I changed rid.toode statitics target to 100:
>
> ALTER TABLE firma2.rid ALTER COLUMN toode SET STATISTICS 100;
> ana
Richard,
Results are provided in bottom of the message to which you replied.
No - the explains there were contrasting a date test BETWEEN versus =.
I changed rid.toode statitics target to 100:
ALTER TABLE firma2.rid ALTER COLUMN toode SET STATISTICS 100;
analyze firma2.rid;
Analyze takes 3
Is it OK to run
ALTER TABLE rid ALTER COLUMN toode SET STATISTICS 1000
in prod database or should I try to decrease 1000 to smaller value ?
rid is big increasing table and is changed frequently, mostly by adding
rows.
pgAdmin shows default_statistic_target value has its default value 10 in
p
Andrus wrote:
> Richard,
>
>> And the results were?
>
> Results are provided in bottom of the message to which you replied.
No - the explains there were contrasting a date test BETWEEN versus =.
>> One problem at a time. Let's get the pattern-matching speed problems on
>> your live server sorte
Richard,
And the results were?
Results are provided in bottom of the message to which you replied.
One problem at a time. Let's get the pattern-matching speed problems on
your live server sorted, then we can look at different queries.
First message in this thread described the issue with q
Scott,
My first thought on the query where a pattern being faster than the query
with an exact value is that the planner does not have good enough
statistics on that column. Without looking at the explain plans further, I
would suggest trying something simple. The fact that it is fasster on
Andrus wrote:
> Richard and Mario,
>
>> You can't use xxx_pattern_ops indexes for non-pattern tests.
>
> I missed regular index. Sorry for that. Now issue with testcase is
> solved. Thank you very much.
>
> I researched issue in live 8.1.4 db a bit more.
> Performed vacuum and whole db reindex.
Richard and Mario,
You can't use xxx_pattern_ops indexes for non-pattern tests.
I missed regular index. Sorry for that. Now issue with testcase is solved.
Thank you very much.
I researched issue in live 8.1.4 db a bit more.
Performed vacuum and whole db reindex.
Tried several times to run t
Andrus schrieb:
Richard,
These are the same but the times are different. I'd be very surprised if
you can reproduce these times reliably.
I re-tried today again and got same results: in production database
pattern query is many times slower that equality query.
toode and rid base contain o
Andrus wrote:
>
> So patter index is 10 .. 20 times (!) faster always.
> No idea why.
Because you don't have a normal index on the product_id column? You
can't use xxx_pattern_ops indexes for non-pattern tests.
> Test data creation script:
The only change to the script was the obvious char(nn)
specific data set or with certain configuration options.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrus
Sent: Monday, November 24, 2008 12:34 PM
To: Richard Huxton
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Increasing pattern index
Richard,
These are the same but the times are different. I'd be very surprised if
you can reproduce these times reliably.
I re-tried today again and got same results: in production database pattern
query is many times slower that equality query.
toode and rid base contain only single produc
Andrus wrote:
> Both queries return same result (19) and return same data.
> Pattern query is a much slower (93 sec) than equality check (13 sec).
> How to fix this ?
> Using 8.1.4, utf-8 encoding, et-EE locale
They're different queries. The fact that they return the same results is
a coincidence
Both queries return same result (19) and return same data.
Pattern query is a much slower (93 sec) than equality check (13 sec).
How to fix this ?
Using 8.1.4, utf-8 encoding, et-EE locale.
Andrus.
SELECT sum(1)
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
WHERE rid.toode =
16 matches
Mail list logo
