Tore Lukashaugen wrote:
Hi,
I am new to postgres having worked with Oracle in the past. I am interested
in understanding Postgres's table partition functionality better.
Specifically, I have a third party application running against my postgres
database, but the database is becoming rather la
Henrik wrote:
> Correct. I changed the statistics to 500 in tbl_file.file_name and now the
> statistics is better. But now my big seq scan on tbl_file_Structure back
> and I don't know why.
Hmm, I think the problem here is that it needs to fetch ~20 tuples
from tbl_file_structure one way o
On Thu, 4 Oct 2007, Tom Lane wrote:
There's some limited smarts in there about deciding that leading columns
of an index don't matter to the sort ordering if they're constrained to
just one value by the query. But it doesn't catch the case you need,
which is that columns of an ORDER BY reques
4 okt 2007 kl. 14:30 skrev Alvaro Herrera:
Henrik wrote:
Ahh I had exactly 8 joins.
Following your suggestion I raised the join_collapse_limit from 8
to 10 and
the planners decision sure changed but now I have some crazy
nested loops.
Maybe I have some statistics wrong?
Yeah. The prob
Ben <[EMAIL PROTECTED]> writes:
> On Thu, 4 Oct 2007, Simon Riggs wrote:
> I thought that might explain it, but then I'm surprised that it can still
> use an index when the first two columns of the index aren't in the query.
> Wouldn't that mean that it might have to walk the entire index to find
Scott Marlowe wrote:
On 10/4/07, Ben <[EMAIL PROTECTED]> wrote:
On Thu, 4 Oct 2007, Tom Lane wrote:
You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message. If you have a planner
problem, show us the *exact* query, the *exact* ta
On 10/4/07, Ben <[EMAIL PROTECTED]> wrote:
> On Thu, 4 Oct 2007, Tom Lane wrote:
>
> > You're being about as clear as mud here, except that you obviously lied
> > about what you were doing in your first message. If you have a planner
> > problem, show us the *exact* query, the *exact* table defini
On Thu, 4 Oct 2007, Simon Riggs wrote:
In the first query, Postgres cannot use the index because the sort order
of the index does not match the sort order of the query. When you change
the sort order of the query so that it matches that of the index, then
the index is used.
If you define your i
On 10/4/07, Ben <[EMAIL PROTECTED]> wrote:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is doin
On Thu, 2007-10-04 at 12:52 -0700, Ben wrote:
> The original, slow query:
>
> explain analyze SELECT * FROM log WHERE clientkey in
> ('450101') AND premiseskey in
> ('450101') and logicaldel = 'N'
> ORDER BY logtime desc, logkey desc, clientkey d
On Thu, 4 Oct 2007, Tom Lane wrote:
You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message. If you have a planner
problem, show us the *exact* query, the *exact* table definition, and
unfaked EXPLAIN ANALYZE output.
I didn't rea
On Thu, 04 Oct 2007 14:03:07 -0500
"Kevin Grittner" <[EMAIL PROTECTED]> wrote:
> It's kind of silly to tell PostgreSQL that its total cache space is
> 1 pages when you've got more than that in shared buffers plus
> all that OS cache space. Try something around 285000 pages for
> effective_cac
>>> On Thu, Oct 4, 2007 at 10:28 AM, in message
<[EMAIL PROTECTED]>, Josh Trutwin
<[EMAIL PROTECTED]> wrote:
> running postgres 8.1.4
> # cat /proc/meminfo
> total:used:free: shared: buffers: cached:
> Mem: 3704217600 3592069120 1121484800 39460864 2316271616
> share
Ben <[EMAIL PROTECTED]> writes:
> No, the tables are recently analyzed and there are a couple hundred
> thousand rows in there. But I think I just figured it out it's a
> 3-column index, and two columns of that index are the same for every row.
> When I drop those two columns from the orderi
On Thu, 4 Oct 2007, Bill Moran wrote:
However, 2 guesses:
1) You never analyzed the table, thus PG has awful statistics and
doesn't know how to pick a good plan.
2) You have so few rows in the table that a seq scan is actually
faster than an index scan, which is why PG uses it instead.
No,
Ben <[EMAIL PROTECTED]> schrieb:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is doing a
In response to Ben <[EMAIL PROTECTED]>:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2 is d
On Thu, 2007-10-04 at 11:00 -0700, Ben wrote:
> If I have this:
>
> create table foo (bar int primary key);
>
> ...then in my ideal world, Postgres would be able to use that index on bar
> to help me with this:
>
> select bar from foo order by bar desc limit 20;
>
> But in my experience, PG8.2
If I have this:
create table foo (bar int primary key);
...then in my ideal world, Postgres would be able to use that index on bar
to help me with this:
select bar from foo order by bar desc limit 20;
But in my experience, PG8.2 is doing a full table scan on foo, then
sorting it, then doing
Hi,
I am new to postgres having worked with Oracle in the past. I am interested
in understanding Postgres's table partition functionality better.
Specifically, I have a third party application running against my postgres
database, but the database is becoming rather large to maintain. I am
thi
On 10/4/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> On Thu, 4 Oct 2007 11:19:22 -0500
> "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
>
> > We need to see examples of what's slow, including explain analyze
> > output for slow queries. Also a brief explanation of the type of
> > load your database s
On Thu, 4 Oct 2007 11:19:22 -0500
"Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> We need to see examples of what's slow, including explain analyze
> output for slow queries. Also a brief explanation of the type of
> load your database server is seeing. I.e. is it a lot of little
> transactions, mo
Oh, and in addition to my previous message, you should use tools like
vmstat, iostat and top to get an idea of what your server is doing.
What kind of drive subsystem do you have? What kind of raid controller? etc...
---(end of broadcast)---
TIP
On 10/4/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> We have a pretty busy linux server running postgres 8.1.4, waiting to
> upgrade until 8.3 to avoid dump/restoring twice.
You should immediate update your version to 8.1.whateverislatest.
That requires no dump / restore and it is a bug fix updat
We have a pretty busy linux server running postgres 8.1.4, waiting to
upgrade until 8.3 to avoid dump/restoring twice.
# cat /proc/meminfo
total:used:free: shared: buffers: cached:
Mem: 3704217600 3592069120 1121484800 39460864 2316271616
Swap: 2516918272 270336 251664
Henrik wrote:
> Ahh I had exactly 8 joins.
> Following your suggestion I raised the join_collapse_limit from 8 to 10 and
> the planners decision sure changed but now I have some crazy nested loops.
> Maybe I have some statistics wrong?
Yeah. The problematic misestimation is exactly the innermo
"Sabin Coanda" writes:
> sorry for the previous incomplete post. I continue with the log:
>
> NOTICE: relation "pg_shdepend" TID 11/1: DeleteTransactionInProgress
> 2657075 --- can't shrink relation
> NOTICE: relation "pg_shdepend" TID 11/2: DeleteTransactionInProgress
> 2657075 --- can't shr
Sabin Coanda wrote:
sorry for the previous incomplete post. I continue with the log:
Not really a performance question, this. Perhaps general/admin lists
would be better next time. No matter...
NOTICE: relation "pg_shdepend" TID 11/1: DeleteTransactionInProgress
2657075 --- can't shrink re
3 okt 2007 kl. 16:15 skrev Tom Lane:
Henrik <[EMAIL PROTECTED]> writes:
Here is the query if needed.
explain analyze SELECT file_name FROM tbl_file_structure JOIN
tbl_file ON pk_file_id = fk_file_id JOIN tbl_structure ON
pk_structure_id = fk_structure_id JOIN tbl_archive ON pk_archive_id
=fk_a
Hi there,
I have a database with lowest possible activity. I run VACUUM FULL AND I get
the following log result:
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your jo
sorry for the previous incomplete post. I continue with the log:
NOTICE: relation "pg_shdepend" TID 11/1: DeleteTransactionInProgress
2657075 --- can't shrink relation
NOTICE: relation "pg_shdepend" TID 11/2: DeleteTransactionInProgress
2657075 --- can't shrink relation
.
NOTICE: relation
31 matches
Mail list logo
