Hello Alexandre,
somewhere.>
I developed a relative simple "file archive" with PostgreSQL (web
application with JSF for user interface). The major structure is one
table with some "key word fields", and 3 blob-fields (because exactly 3
files belong to one record). I have do deal with million
Since you have two redundant tests, the selectivity is being
double-counted, leading to a too-small rows estimate and a not very
appropriate choice of join plan.
I see, thanks for the explanation. I did notice though that in the second
case, with 1 redundant test removed, the estimate is still
"henk de wit" <[EMAIL PROTECTED]> writes:
> In that case the prediction is 2 rows, which is only 1 row more than in the
> previous case. Yet the plan is much better and performance improved
> dramatically. Is there a reason/explanation for that?
Well, it's just an estimated-cost comparison. If
In the actual event, with
359 rows out of the scan, the nestloop way is just horrid because it
repeats the other side 359 times :-(
Indeed. :(
Btw, I tried to apply the removal of the redundant check in the larger query
(the one from which I extracted the part shown earlier) but it only perf
"henk de wit" <[EMAIL PROTECTED]> writes:
> I understand the above looks like a complicated mess, but would you
> have any pointers of what I could possibly do next to force a better plan?
Taking a closer look, it seems the problem is the underestimation of the
number of rows resulting from this
You might be able to improve matters by increasing the statistics target
for this table.
I have tried to increase the statistics for the status column to the maximum
of 1000. After that I performed an analyze, vacuum analyze and vacuum full
analyze on the table. Unfortunately this didn't seem
One interesting other thing to note; if I remove the banners_links.status =
0 condition from the query altogether the execution times improve
dramatically again. The results are not correct right now, but if worse
comes to worst I can always remove the unwanted rows in a procedural
language (it
"henk de wit" <[EMAIL PROTECTED]> writes:
>> --- the thing is evidently assuming
>> that only about 1 in 200 rows have status = '0', which might be accurate
>> as a global average but not for this particular merchant. What exactly
>> is the relationship between status and merchant_id, anyway?
> T
Hi Tom / Steve,
Am one of the silent readers of performance issues that come up on this list
(and are discussed in detail) ... just like this one.
If and when you do come up with a solution, please do post some details
about them here... (i say that coz it seems that for obvious reasons, things
I have a table where I store email, the bodies are mostly kept in a
toast table.The toast table is 940 Meg in size. The whole database
is about 1.2 Gig in size. When I back the database up using pg_dump in
custom output mode, I pipe the output into gzip. My backups are only
about 600 meg
Hello Alexandre,
somewhere.>
I developed a relative simple "file archive" with PostgreSQL (web
application with JSF for user interface). The major structure is one
table with some "key word fields", and 3 blob-fields (because exactly 3
files belong to one record). I have do deal with million
Hi,
I'm currently dealing with performance issues of postgres and looking
for some advice.
Platform
Postgres: 7.0.2
OS: FreeBSD4.4
DB: size - about 50M, most frequently updated tables are of an average
size of 1000-2000 rows and there are not many of them, about 15 in total
Descripti
I have two tables, staff (530 rows) and location (2.5 million rows). I
do a query that joins the two together, as so:
SELECT s.ProprietorId, l.LocationId, s.RoleId
FROM Location l
INNER JOIN (
SELECT *
FROM Staff
) s ON l.ProprietorId = s.
I have a table that contains a column for keywords that I expect to become
quite large and will be used for web searches. I will either index the
column or come up with a simple hashing algorithm add the hash key to the
table and index that column.
I am thinking the max length in the keyword col
Merlin Moncure wrote:
In the context of this debate, I see this argument all the time, with
the implied suffix: 'If only we used integer keys we would not have
had this problem...'. Either the customer identifies parts with a
part number or they don't...and if they do identify parts with a
numbe
"Sergey Tsukinovsky" <[EMAIL PROTECTED]> writes:
> I'm currently dealing with performance issues of postgres and looking
> for some advice.
> Postgres: 7.0.2
Stop right there. You have *no* business asking for help on an
installation you have not updated in more than six years.
On 4/20/07, chrisj <[EMAIL PROTECTED]> wrote:
I have a table that contains a column for keywords that I expect to become
quite large and will be used for web searches. I will either index the
column or come up with a simple hashing algorithm add the hash key to the
table and index that column.
On Thu, 19 Apr 2007, Sergey Tsukinovsky wrote:
I know that 7.0.2 is an old version and therefore ran the same test on
7.3.18 - the performance behavior was similar.
Why have you choosen just another very old version for performance
comparison and not the latest stable release?
Kind regards
18 matches
Mail list logo
