In response to Mike Charnoky <[EMAIL PROTECTED]>:
> The db server is pretty beefy: 2x Xeon 3.20 GHz with 6G RAM. The io
> subsystem is a 550G 4-disk SATA 150 RAID 10 array connected via a 3ware
> 9500S-8 controller (Seagate Nearline ST3400632NS drives). Currently,
> shared_buffers is set to 5000
The db server is pretty beefy: 2x Xeon 3.20 GHz with 6G RAM. The io
subsystem is a 550G 4-disk SATA 150 RAID 10 array connected via a 3ware
9500S-8 controller (Seagate Nearline ST3400632NS drives). Currently,
shared_buffers is set to 5 (nearly 400M)
As for the data stored in this large table
On Mon, 01 Oct 2007, Mike Charnoky wrote:
> I altered the table in question, with "set statistics 100" on the
> timestamp column, then ran analyze. This seemed to help somewhat. Now,
> queries don't seem to hang, but it still takes a long time to do the count:
> * "where evtime between '2007-09
On Mon, Oct 01, 2007 at 01:34:32PM -0400, Bill Moran wrote:
> This sounds like a caching issue. My guess at what's happening is that
> other operations are pushing this data out of the shared_buffers, so
> when you run it, the system has to pull a bunch of tuples off the disk
> to check them. If
"Mike Charnoky" <[EMAIL PROTECTED]> writes:
> Here is the output from EXPLAIN ANALYZE. This is the same query run
> back to back, first time takes 42 minutes, second time takes less than 2
> minutes!
That doesn't really sound strange at all. It sounds like you have a very slow
disk and very lar
Mike Charnoky wrote:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query again, it finishes in 1-2 minutes!
This is just
In response to Mike Charnoky <[EMAIL PROTECTED]>:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query again, it finishes
On Monday 01 October 2007, Mike Charnoky <[EMAIL PROTECTED]> wrote:
> This is strange... count(*) operations over a period of one day's worth
> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
> first time the data is queried it takes about 40 minutes. If I try the
> query a
This is strange... count(*) operations over a period of one day's worth
of data now take ~1-2 minutes to run or ~40 minutes. It seems that the
first time the data is queried it takes about 40 minutes. If I try the
query again, it finishes in 1-2 minutes!
Again, nothing else is happening on this
"Mike Charnoky" <[EMAIL PROTECTED]> writes:
> I altered the table in question, with "set statistics 100" on the
> timestamp column, then ran analyze. This seemed to help somewhat. Now,
> queries don't seem to hang, but it still takes a long time to do the count:
> * "where evtime between '2007-
I altered the table in question, with "set statistics 100" on the
timestamp column, then ran analyze. This seemed to help somewhat. Now,
queries don't seem to hang, but it still takes a long time to do the count:
* "where evtime between '2007-09-26' and '2007-09-27'"
took 37 minutes to run (r
Albe Laurenz wrote:
> Alban Hertroys wrote:
>> A. Kretschmer wrote:
>>> Again: an index can't help! Because of MVCC: 'select count(*)'
> without
>>> WHERE-condition forces an seq. table-scan.
>> That has very little to do with MVCC.
>>
>> [...] For that it makes no difference whether a seq
>> scan
Alban Hertroys wrote:
> A. Kretschmer wrote:
>> Again: an index can't help! Because of MVCC: 'select count(*)'
without
>> WHERE-condition forces an seq. table-scan.
>
> That has very little to do with MVCC.
>
> [...] For that it makes no difference whether a seq
> scan or an index scan is perform
"Alban Hertroys" <[EMAIL PROTECTED]> writes:
> Mike Charnoky wrote:
>> With respect to the ALTER TABLE SET STATISTICS... how do I determine a
>> good value to use? This wasn't really clear in the pg docs. Also, do I
>> need to run ANALYZE on the table after I change the statistics?
>>
>> Here a
Mike Charnoky wrote:
> With respect to the ALTER TABLE SET STATISTICS... how do I determine a
> good value to use? This wasn't really clear in the pg docs. Also, do I
> need to run ANALYZE on the table after I change the statistics?
>
> Here are the EXPLAINs from the queries:
>
> db=# explain s
A. Kretschmer wrote:
> Again: an index can't help! Because of MVCC: 'select count(*)' without
> WHERE-condition forces an seq. table-scan.
That has very little to do with MVCC.
If I understand correctly, MVCC is about the availability of records in
the current transaction. For that it makes no d
On 9/28/07, Mike Charnoky <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I am still having problems performing a count(*) on a large table. This
> is a followup from a recent thread:
>
> http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
>
> Since the last time these problems happened, we hav
With respect to the ALTER TABLE SET STATISTICS... how do I determine a
good value to use? This wasn't really clear in the pg docs. Also, do I
need to run ANALYZE on the table after I change the statistics?
Here are the EXPLAINs from the queries:
db=# explain select count(*) from prediction_accu
In response to Mike Charnoky <[EMAIL PROTECTED]>:
> The autovacuum is turned on. Since this is pg8.1, I don't know when the
> table was actually last vacuumed. I *did* run analyze on the table,
> though. Also, nothing has been deleted in this table... so vacuum
> should have no affect, right?
The autovacuum is turned on. Since this is pg8.1, I don't know when the
table was actually last vacuumed. I *did* run analyze on the table,
though. Also, nothing has been deleted in this table... so vacuum
should have no affect, right?
Mike
Sean Davis wrote:
> Mike Charnoky wrote:
>> Hi,
>>
>
Mike Charnoky wrote:
> Hi,
>
> I am still having problems performing a count(*) on a large table. This
> is a followup from a recent thread:
>
> http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
>
> Since the last time these problems happened, we have tweaked some
> postgresql c
"Alvaro Herrera" <[EMAIL PROTECTED]> writes:
> A. Kretschmer wrote:
>> am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
>> > Hi,
>> >
>> > I am still having problems performing a count(*) on a large table. This
>> >
>> > Now, certain count(*) queries are failing to com
am Fri, dem 28.09.2007, um 12:50:34 -0400 mailte Alvaro Herrera folgendes:
> A. Kretschmer wrote:
> > am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
> > > Hi,
> > >
> > > I am still having problems performing a count(*) on a large table. This
> > >
> > > Now, certain
On Fri, Sep 28, 2007 at 12:50:34PM -0400, Alvaro Herrera wrote:
>
> But he does have a WHERE condition. THe problem is, probably, that the
> condition is not selective enough so the planner chooses to do a
> seqscan.
Or else the planner has a bad idea of how selective the condition is.
I've fou
A. Kretschmer wrote:
> am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
> > Hi,
> >
> > I am still having problems performing a count(*) on a large table. This
> >
> > Now, certain count(*) queries are failing to complete for certain time
> > ranges (I killed the query
am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
> Hi,
>
> I am still having problems performing a count(*) on a large table. This
>
> Now, certain count(*) queries are failing to complete for certain time
> ranges (I killed the query after about 24 hours). The table i
26 matches
Mail list logo
