rence Cohan
Subject: RE: [BUGS] Postgres not using indexes
Lawrence Cohan wrote:
> We managed to put together a new test server running PG 9.0.2 on
> 2socketsx6cores = 12CPU with 64 GB RAM against a 3PAR 10TB SAN. We
> kept the settings I submitted already (and enclosed below) and
>
Lawrence Cohan wrote:
> We managed to put together a new test server running PG 9.0.2 on
> 2socketsx6cores = 12CPU with 64 GB RAM against a 3PAR 10TB SAN. We
> kept the settings I submitted already (and enclosed below) and
> after 12 hours of pounding the box with PGBENCH running 8 scripts
> to
r
Sent: March-30-11 4:12 PM
To: pgsql-bugs@postgresql.org; Lawrence Cohan
Subject: Re: [BUGS] Postgres not using indexes
Lawrence Cohan wrote:
> looks like we will need to change at least the two values below
> and maybe play with work_mem to see if it solves our issues.
You will probably
Thank you for all your suggestions and I hope the "set enable_seqscan = off;"
will work for the time being until we can make PG config changes and more
testing in the near future. We expect indeed much better performance with index
being used on the 33+million rows table vs seq scan and I will p
Greg Stark wrote:
> On Thu, Mar 31, 2011 at 11:33 PM, Kevin Grittner
> wrote:
>> Greg Stark wrote:
>>
>>> your query does require reading all the data.
>>
>> Huh? It requires reading all the data from at least *one* of the
>> tables.
>
> The query he posted a plan for was:
>
> EXPLAIN ANALYZE
On Thu, Mar 31, 2011 at 11:33 PM, Kevin Grittner
wrote:
> Greg Stark wrote:
>
>> your query does require reading all the data.
>
> Huh? It requires reading all the data from at least *one* of the
> tables.
The query he posted a plan for was:
EXPLAIN ANALYZE select oi.id from order_items oi inn
Greg Stark wrote:
> your query does require reading all the data.
Huh? It requires reading all the data from at least *one* of the
tables. I could conceivably be faster to read all the data from the
table with 23,980 rows and randomly pick out the necessary 33,768
rows from the table with 33
On Wed, Mar 30, 2011 at 7:32 PM, Lawrence Cohan wrote:
> Please see updated attachment that includes the tables involved in the simple
> query below and all their indexes. We believe that the performance issue is
> due to the query not using any index but doing seq scans instead and this is
> v
: March-30-11 4:12 PM
To: pgsql-bugs@postgresql.org; Lawrence Cohan
Subject: Re: [BUGS] Postgres not using indexes
Lawrence Cohan wrote:
> looks like we will need to change at least the two values below
> and maybe play with work_mem to see if it solves our issues.
You will probably get
Lawrence Cohan wrote:
> looks like we will need to change at least the two values below
> and maybe play with work_mem to see if it solves our issues.
You will probably get better throughput by bumping up
shared_buffers to the recommended setting, but beware of "stalls" in
query processing at
Harry Rossignol wrote:
> I'm just a lowly end user. Bumping the default statistics target
> or using ALTER TABLE SET STATISTICS has made large differences in
> query performance on large tables.
The default has been bumped up in later versions, so that shouldn't
be as big a problem as it once
'log_checkpoints';'on'
'log_destination';'syslog'
'log_line_prefix';'user=%u,db=%d'
'log_min_duration_statement';'1s'
'maintenance_work_mem';'256MB'
'max_connections';'1200'
'ma
7;syslog'
'log_line_prefix';'user=%u,db=%d '
'log_min_duration_statement';'1s'
'maintenance_work_mem';'256MB'
'max_connections';'1200'
'max_stack_depth';'2MB'
'port';'5432'
'server
Lawrence Cohan wrote:
> Please see updated attachment that includes the tables involved in
> the simple query below and all their indexes.
Well, that rules out a couple common problems (comparisons between
different types and incorrect indexing).
> We believe that the performance issue is du
.
-Original Message-
From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov]
Sent: March-30-11 1:33 PM
To: pgsql-bugs@postgresql.org; Lawrence Cohan
Subject: RE: [BUGS] Postgres not using indexes
Lawrence Cohan wrote:
> From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov]
>> [conf
Lawrence Cohan wrote:
> From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov]
>> [configuration advice]
>> If, after reading the above-cited page and tuning your server you
>> still have performance problems, pick one query to work on first,
>> and follow the step outlined here:
>>
>> htt
Message-
From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov]
Sent: March-30-11 12:45 PM
To: pgsql-bugs@postgresql.org; Lawrence Cohan
Subject: Re: [BUGS] Postgres not using indexes
Lawrence Cohan wrote:
> We have a huge performance issues in Postgres that surfaced due to
> existing i
[mailto:pavel.steh...@gmail.com]
Sent: March-30-11 12:08 PM
To: Lawrence Cohan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Postgres not using indexes
Hello
2011/3/30 Lawrence Cohan :
> We have a huge performance issues in Postgres that surfaced due to existing
> indexes not being used like
Lawrence Cohan wrote:
> We have a huge performance issues in Postgres that surfaced due to
> existing indexes not being used
This doesn't sound like a bug; it sounds like you haven't tuned your
server.
For starters, you should check out this page:
http://wiki.postgresql.org/wiki/Tuning_You
rence Cohan; pgsql-bugs@postgresql.org
Subject: RE: [BUGS] Postgres not using indexes
I force postgresql to use indexes instead of sequential scans by setting
enable_seqscan = off in postgresql.conf and it helps in a lot of cases.
Probably not the best practice, but it does improve a lot o
Hello
2011/3/30 Lawrence Cohan :
> We have a huge performance issues in Postgres that surfaced due to existing
> indexes not being used like in the example below in both 8.35 and 9.0
> versions.
>
>
>
> Client_Orders table with and int ID as PK which is the order_id and indexed
> – about 155,000 r
I force postgresql to use indexes instead of sequential scans by setting
enable_seqscan = off in postgresql.conf and it helps in a lot of cases.
Probably not the best practice, but it does improve a lot of the queries
we will execute on a regular basis. It forces the planner to prefer
indexes. I've
22 matches
Mail list logo
