On Tue, Mar 11, 2008 at 02:19:09PM +, Matthew wrote:
> of rows with IS NULL, then someone changes a row, then you find the count
> of rows with IS NOT NULL. Add the two together, and there may be rows that
> were counted twice, or not at all.
Only if you count in READ COMMITTED.
A
--
Sen
Matthew wrote:
No, actually I was referring to a race condition. So, you find the count
of rows with IS NULL, then someone changes a row, then you find the
count of rows with IS NOT NULL. Add the two together, and there may be
rows that were counted twice, or not at all.
Not a problem if you
On Tue, 11 Mar 2008, Tino Wildenhain wrote:
And certain, qualified definitions of "accurate" as well. Race condition?
You mean in a three-state-logic? null, not null and something different?
True, False, and FILE_NOT_FOUND.
No, actually I was referring to a race condition. So, you find the c
Hi,
Matthew wrote:
On Tue, 11 Mar 2008, Bill Moran wrote:
In response to "Robins Tharakan" <[EMAIL PROTECTED]>:
Sorry, if I am missing something here, but shouldn't something like this
allow us to get a (fast) accurate count ?
SELECT COUNT(*) from table WHERE indexed_field IS NULL
+
SELECT C
On Tue, 11 Mar 2008, Bill Moran wrote:
In response to "Robins Tharakan" <[EMAIL PROTECTED]>:
Sorry, if I am missing something here, but shouldn't something like this
allow us to get a (fast) accurate count ?
SELECT COUNT(*) from table WHERE indexed_field IS NULL
+
SELECT COUNT(*) from table WH
In response to "Robins Tharakan" <[EMAIL PROTECTED]>:
> Hi,
>
> I have been reading this conversation for a few days now and I just wanted
> to ask this. From the release notes, one of the new additions in 8.3 is
> (Allow col IS NULL to use an index (Teodor)).
>
> Sorry, if I am missing somethin
A Dimarts 11 Març 2008 04:11, Scott Marlowe va escriure:
> On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I have been reading this conversation for a few days now and I just
> > wanted to ask this. From the release notes, one of the new additions in
> >
On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have been reading this conversation for a few days now and I just wanted
> to ask this. From the release notes, one of the new additions in 8.3 is
> (Allow col IS NULL to use an index (Teodor)).
>
> Sorry, if I
On Tue, 11 Mar 2008 08:27:05 +0530
"Robins Tharakan" <[EMAIL PROTECTED]> wrote:
> SELECT COUNT(*) from table WHERE indexed_field IS NULL
> +
> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
If the selectivity is appropriate yes. However if you have 1 million
rows, and 200k of those ro
Robins Tharakan wrote:
Hi,
I have been reading this conversation for a few days now and I just
wanted to ask this. From the release notes, one of the new additions
in 8.3 is (Allow col IS NULL to use an index (Teodor)).
Sorry, if I am missing something here, but shouldn't something like
thi
(fast) accurate count ?
SELECT COUNT(*) from table WHERE indexed_field IS NULL
+
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
*Robins Tharakan*
-- Forwarded message --
From: Greg Smith <[EMAIL PROTECTED]>
Date: Tue, Mar 11, 2008 at 4:31 AM
Subject: Re: [PER
On Mon, 10 Mar 2008, Joe Mirabal wrote:
I run queries on the data nad get immediate max and min as well as other
aggrgate functions very quickly, however a select count(*) of the table
takes forever usually nearly an hour or more.
Are you sure the form of "select count(*)" you're using is act
On Mon, Mar 10, 2008 at 1:54 PM, Joe Mirabal <[EMAIL PROTECTED]> wrote:
> Gregory,
>
> I just joined this listserv and was happy to see this posting. I have a
> 400GB table that I have indexed (building the index took 27 hours) , Loading
> the table with 10 threads took 9 hours. I run queries on
On Mon, 10 Mar 2008, Bill Moran wrote:
Some searches through the archives should turn up details on these
methods.
I've collected up what looked like the best resources on this topic into
the FAQ entry at http://www.postgresqldocs.org/index.php/Slow_Count
General Bits has already done two g
In response to "Joe Mirabal" <[EMAIL PROTECTED]>:
> Gregory,
>
> I just joined this listserv and was happy to see this posting. I have a
> 400GB table that I have indexed (building the index took 27 hours) , Loading
> the table with 10 threads took 9 hours. I run queries on the data nad get
> i
Gregory,
I just joined this listserv and was happy to see this posting. I have a
400GB table that I have indexed (building the index took 27 hours) , Loading
the table with 10 threads took 9 hours. I run queries on the data nad get
immediate max and min as well as other aggrgate functions very q
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Well, scanning an index to get a count might be significantly faster
> than scanning the main table, but it's hardly "instantaneous". It's
> still going to take time proportional to the table size.
Hm, Mark's comment about bitmap indexes makes that not en
On 6-3-2008 16:28 Craig James wrote:
On the one hand, I understand that Postgres has its architecture, and I
understand the issue of row visibility, and so forth. On the other
hand, my database is just sitting there, nothing going on, no
connections except me, and... it takes FIFTY FIVE SECOND
Mark Mielke wrote:
Josh Berkus wrote:
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
Nope. Oracle's MVCC is implemen
Greg Smith <[EMAIL PROTECTED]> writes:
> I know when I'm playing with pgbench the primary key index on the big
> accounts table is 1/7 the size of the table, and when using that table
> heavily shared_buffers ends up being mostly filled with that index. The
> usage counts are so high on the inde
Josh Berkus wrote:
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
Nope. Oracle's MVCC is implemented through rollback
On Fri, 7 Mar 2008, Tom Lane wrote:
Well, scanning an index to get a count might be significantly faster
than scanning the main table, but it's hardly "instantaneous". It's
still going to take time proportional to the table size.
If this is something that's happening regularly, you'd have to
Josh Berkus <[EMAIL PROTECTED]> writes:
> Tom,
>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>> large tables. So why can't Postgres do what they do?
>>
>> AFAIK the above claim is false for Oracle. They have the same
>> transactional issues we do.
> Nope. Oracle's MVCC
Tom,
> > Count() on Oracle and MySQL is almost instantaneous, even for very
> > large tables. So why can't Postgres do what they do?
>
> AFAIK the above claim is false for Oracle. They have the same
> transactional issues we do.
Nope. Oracle's MVCC is implemented through rollback segments, rath
Craig James wrote:
Tom Lane wrote:
Craig James <[EMAIL PROTECTED]> writes:
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
My ex
Craig James wrote:
Tom Lane wrote:
Craig James <[EMAIL PROTECTED]> writes:
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
My ex
Tom Lane wrote:
Craig James <[EMAIL PROTECTED]> writes:
Count() on Oracle and MySQL is almost instantaneous, even for very large
tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
My experience doesn't ma
Craig James wrote:
My question is: What do the other databases do that Postgres can't do,
and why not?
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
I think Mysql can only do that for the myisam engine - innodb and
Craig James <[EMAIL PROTECTED]> writes:
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
regards, tom lane
On Thu, 06 Mar 2008 07:28:50 -0800
Craig James <[EMAIL PROTECTED]> wrote:
> In the 3 years I've been using Postgres, the problem of count() performance
> has come up more times than I can recall, and each time the answer is, "It's
> a sequential scan -- redesign your application."
>
> My questio
On Thu, 2008-03-06 at 07:28 -0800, Craig James wrote:
...
> My question is: What do the other databases do that Postgres can't do, and
> why not?
>
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
...
I can vouch that Or
On Thu, Mar 6, 2008 at 3:49 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
>
> You only get this accelerated significantly when using MyISAM, which can
> tell you an exact count of all the rows it hasn't corrupted yet.
Please don't do that again. I'm going to have to spend the next hour
cleaning coff
On Thu, 6 Mar 2008, Steinar H. Gunderson wrote:
On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
Count() on Oracle and MySQL is almost instantaneous, even for very large
tables. So why can't Postgres do what they do?
In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB
In response to Craig James <[EMAIL PROTECTED]>:
> In the 3 years I've been using Postgres, the problem of count() performance
> has come up more times than I can recall, and each time the answer is, "It's
> a sequential scan -- redesign your application."
>
> My question is: What do the other d
On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB table.)
/* Steinar */
--
Homepage: http://www
Craig James wrote:
> This is a real problem. Countless people (including me) have
> spent significant effort rewriting applications because of this
> performance flaw in Postgres. Over and over, the response is,
> "You don't really need to do that ... change your application."
> Well, sure, it's
In the 3 years I've been using Postgres, the problem of count() performance has come up
more times than I can recall, and each time the answer is, "It's a sequential scan
-- redesign your application."
My question is: What do the other databases do that Postgres can't do, and why
not?
Count()
On Thu, 6 Mar 2008, sathiya psql wrote:
any way will you explain., what is this COST, actual time and other
stuffs
There's a long list of links to tools and articles on this subject at
http://www.postgresqldocs.org/index.php/Using_EXPLAIN
--
* Greg Smith [EMAIL PROTECTED] http://www.gre
On Thu, 6 Mar 2008, sathiya psql wrote:
is there any article saying the difference between this 7.3 and 8.4
I've collected a list of everything on this topic I've seen at
http://www.postgresqldocs.org/index.php/Version_8.3_Changes
The Feature Matrix linked to there will be a quicker way to
A. Kretschmer wrote:
am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
TRIGGER i can use if i want the count of the whole table, but i require for
some of the rows with WHERE condition
so how to do that ???
Okay, in this case a TRIGGER are a bad idea. You c
sathiya psql escribió:
> > Yes it is the latest stable version.
>
> is there any article saying the difference between this 7.3 and 8.4
http://www.postgresql.org/docs/8.3/static/release.html
In particular,
http://www.postgresql.org/docs/8.3/static/release-8-3.html
http://www.postgresql.org/docs/
am Thu, dem 06.03.2008, um 18:13:50 +0530 mailte sathiya psql folgendes:
>
> Yes it is the latest stable version.
>
>
> is there any article saying the difference between this 7.3 and 8.4
http://developer.postgresql.org/pgdocs/postgres/release.html
Andreas
--
Andreas Kretschmer
Kontakt:
Of course, the official documentation covers that information in its
release notes
http://www.postgresql.org/docs/8.3/static/release.html
best wishes
Harald
On Thu, Mar 6, 2008 at 1:43 PM, sathiya psql <[EMAIL PROTECTED]> wrote:
>
>
> >
> >
> >
> > Yes it is the latest stable version.
>
> is th
> Yes it is the latest stable version.
>
is there any article saying the difference between this 7.3 and 8.4
Hi,
On 6-Mar-08, at 6:58 AM, sathiya psql wrote:
The only thing that is going to help you is really fast disks, and
more memory, and you should consider moving to 8.3 for all the other
performance benefits.
Is 8.3 is a stable version or what is the latest stable version of
postgres ??
Yes
On 6-Mar-08, at 1:43 AM, sathiya psql wrote:
is there any way to explicitly force the postgres to use index scan
If you want to count all the rows in the table there is only one way
to do it (without keeping track yourself with a trigger ); a seq scan.
An index will not help you.
The o
am Thu, dem 06.03.2008, um 12:36:48 +0530 mailte sathiya psql folgendes:
>
> QUERY PLAN
> --
> Aggregate (cost=20
QUERY PLAN
--
Aggregate (cost=205756.95..205756.95 rows=1 width=0) (actual time=
114675.042..114675.042 rows=1 loop
am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
> TRIGGER i can use if i want the count of the whole table, but i require for
> some of the rows with WHERE condition
>
> so how to do that ???
Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
this r
am Thu, dem 06.03.2008, um 12:13:17 +0530 mailte sathiya psql folgendes:
> is there any way to explicitly force the postgres to use index scan
Not realy, PG use a cost-based optimizer and use an INDEX if it make
sense.
>
> On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <
> [EMAIL PROTECTED]> w
-- Forwarded message --
From: sathiya psql <[EMAIL PROTECTED]>
Date: Thu, Mar 6, 2008 at 12:17 PM
Subject: Re: [PERFORM] count * performance issue
To: "A. Kretschmer" <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
TRIGGER i can use if i want the count of the whol
is there any way to explicitly force the postgres to use index scan
On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <
[EMAIL PROTECTED]> wrote:
> am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:
> >
> >
> > There aren't a general solution. If you realy need the exac
am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:
>
>
> There aren't a general solution. If you realy need the exact count of
> tuples than you can play with a TRIGGER and increase/decrease the
> tuple-count for this table in an extra table.
>
>
>
On Thu, Mar 6, 2008 at 5:31 PM, sathiya psql <[EMAIL PROTECTED]> wrote:
> will you please tell, what is autovacuuming... and wat it ll do... is
> there any good article in this
>
>
>
Read this -->
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#AUTOVACUUM
--
Shoaib Mir
will you please tell, what is autovacuuming... and wat it ll do... is there
any good article in this
On Thu, Mar 6, 2008 at 11:56 AM, Shoaib Mir <[EMAIL PROTECTED]> wrote:
> On Thu, Mar 6, 2008 at 5:19 PM, sathiya psql <[EMAIL PROTECTED]>
> wrote:
>
> > buy every time i need to put ANALYZE...
There aren't a general solution. If you realy need the exact count of
tuples than you can play with a TRIGGER and increase/decrease the
tuple-count for this table in an extra table.
Of course, this means accepting the cost of obtaining update locks on
the count table.
The orig
On Thu, Mar 6, 2008 at 5:19 PM, sathiya psql <[EMAIL PROTECTED]> wrote:
> buy every time i need to put ANALYZE...
> this takes the same time as count(*) takes, what is the use ??
>
>
>
Dont you have autovacuuming running in the background which is taking care
of the analyze as well?
If not then h
buy every time i need to put ANALYZE...
this takes the same time as count(*) takes, what is the use ??
On Thu, Mar 6, 2008 at 11:45 AM, Shoaib Mir <[EMAIL PROTECTED]> wrote:
> On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer <
> [EMAIL PROTECTED]> wrote:>
>
> > > am having a table with nearly 50 lak
On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer <
[EMAIL PROTECTED]> wrote:>
> > am having a table with nearly 50 lakh records,
> >
> > it has more than 15 columns, i want to count how many records are there,
> it is
> > taking nearly 17 seconds to do that...
> >
> > i know that to get a approximate
am Thu, dem 06.03.2008, um 11:13:01 +0530 mailte sathiya psql folgendes:
> count(*) tooks much time...
>
> but with the where clause we can make this to use indexing,... what where
> clause we can use??
An index without a WHERE can't help to avoid a seq. scan.
>
> Am using postgres 7.4 in Deb
sathiya psql wrote:
count(*) tooks much time...
but with the where clause we can make this to use indexing,... what
where clause we can use??
Am using postgres 7.4 in Debian OS with 1 GB RAM,
am having a table with nearly 50 lakh records,
Looks suspiciously like a question asked yesterday:
count(*) tooks much time...
but with the where clause we can make this to use indexing,... what where
clause we can use??
Am using postgres 7.4 in Debian OS with 1 GB RAM,
am having a table with nearly 50 lakh records,
it has more than 15 columns, i want to count how many records are there, it
On Mon, Mar 27, 2006 at 12:20:54PM -0700, Brendan Duddridge wrote:
> Does that mean that even though autovacuum is turned on, you still
> should do a regular vacuum analyze periodically?
Doing a periodic vacuumdb -avz and keeping an eye on the last few lines
isn't a bad idea. It would also be he
Gábriel Ákos wrote:
> I thought that too. Autovacuum is running on our system but it didn't do
> the trick. Anyway the issue is solved, thank you all for helping. :)
Hi, Gabriel, it may be that your Free Space Map (FSM) setting is way to
low.
Try increasing it.
Btw, VACUUM outputs a Warning if
s weekend, if I can spare some time.
- Mikael
-Original Message-
From: Matthew T. O'Connor [mailto:[EMAIL PROTECTED]
Sent: den 28 mars 2006 00:43
To: Mikael Carneholm
Cc: Postgresql Performance
Subject: Re: [PERFORM] count(*) performance
Mikael Carneholm wrote:
> This is where
"Matthew T. O'Connor" writes:
> It is fairly easy to implement, however it has been discussed before and
> decided that it wasn't necessary. What the system cares about is how
> long it's been since the last vacuum in terms of XIDs not time.
I think Alvaro is intending to do the latter (store
Mikael Carneholm wrote:
This is where a "last_vacuumed" (and "last_analyzed") column in
pg_statistic(?) would come in handy. Each time vacuum or analyze has
finished, update the row for the specific table that was
vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed
column. No mo
ut that has fooled me before... :-)
- Mikael
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Guido
Neitzer
Sent: den 27 mars 2006 21:44
To: Brendan Duddridge
Cc: Postgresql Performance
Subject: Re: [PERFORM] count(*) performance
On 27.03.2006, at 21:20 Uh
On 27.03.2006, at 21:20 Uhr, Brendan Duddridge wrote:
Does that mean that even though autovacuum is turned on, you still
should do a regular vacuum analyze periodically?
It seems that there are situations where autovacuum does not a really
good job.
However, in our application I have made
Brendan Duddridge wrote:
> Does that mean that even though autovacuum is turned on, you still
> should do a regular vacuum analyze periodically?
No, it probably means you have set FSM settings too low, or not tuned
the autovacuum parameters to your specific situation.
A bug in the autovacuum da
Gábriel Ákos wrote:
Luke Lonergan wrote:
Gabriel,
On 3/27/06 10:05 AM, "Gábriel Ákos" <[EMAIL PROTECTED]> wrote:
That gave me an idea. I thought that autovacuum is doing it right, but I
issued a vacuum full analyze verbose , and it worked all the day.
After that I've tweaked memory settings a
Does that mean that even though autovacuum is turned on, you still
should do a regular vacuum analyze periodically?
Thanks,
Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED]
ClickSpace Interactive Inc.
Suite L1
Luke Lonergan wrote:
Gabriel,
On 3/27/06 10:05 AM, "Gábriel Ákos" <[EMAIL PROTECTED]> wrote:
That gave me an idea. I thought that autovacuum is doing it right, but I
issued a vacuum full analyze verbose , and it worked all the day.
After that I've tweaked memory settings a bit too (more fsm_pa
Gabriel,
On 3/27/06 10:05 AM, "Gábriel Ákos" <[EMAIL PROTECTED]> wrote:
> That gave me an idea. I thought that autovacuum is doing it right, but I
> issued a vacuum full analyze verbose , and it worked all the day.
> After that I've tweaked memory settings a bit too (more fsm_pages)
Oops! I repl
Jim C. Nasby wrote:
But in this case, I'd bet money that if it's taking 4 minutes something
else is wrong. Have you been vacuuming that table frequently enough?
That gave me an idea. I thought that autovacuum is doing it right, but I
issued a vacuum full analyze verbose , and it worked all the
Luke Lonergan wrote:
To test your disk speed, use the following commands and report the times
here:
time bash -c "dd if=/dev/zero of=bigfile bs=8k count=50 && sync"
[EMAIL PROTECTED]:/fast # time bash -c "dd if=/dev/zero of=bigfile bs=8k
count=50 && sync"
50+0 records in
5
Gabriel,
On 3/27/06 5:34 AM, "Gábriel Ákos" <[EMAIL PROTECTED]> wrote:
> Question: I have a table with 2.5M rows. count(*) on this table is
> running 4 minutes long. (dual opteron, 4gig ram, db on 4 disk raid10
> array (sata, not scsi)) Is this normal? How could I make it run faster?
> Maybe make
On Mon, Mar 27, 2006 at 03:34:32PM +0200, G?briel ?kos wrote:
> Hi,
>
> I guess this is an age-old 100times answered question, but I didn't find
> the answer to it yet (neither in the FAQ nor in the mailing list archives).
>
> Question: I have a table with 2.5M rows. count(*) on this table is
>
Hi,
I guess this is an age-old 100times answered question, but I didn't find
the answer to it yet (neither in the FAQ nor in the mailing list archives).
Question: I have a table with 2.5M rows. count(*) on this table is
running 4 minutes long. (dual opteron, 4gig ram, db on 4 disk raid10
arr
79 matches
Mail list logo
