Michael Stone schrieb:
On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote:
Craig A. James schrieb:
You guys can correct me if I'm wrong, but the key feature that's
missing from Postgres's flexible indexing is the ability to maintain
state across queries. Something like this:
On Fri, Mar 23, 2007 at 01:01:02PM +0100, Tino Wildenhain wrote:
This discussion is a bit theoretical until we see the actual problem
and the proposed solution here.
It's good to see you back off a bit from your previous stance of
assuming that someone doesn't know what they're doing and that
Michael Stone schrieb:
On Fri, Mar 23, 2007 at 01:01:02PM +0100, Tino Wildenhain wrote:
This discussion is a bit theoretical until we see the actual problem
and the proposed solution here.
It's good to see you back off a bit from your previous stance of
assuming that someone doesn't know
Hi,
I just try to find out why a simple count(*) might last that long.
At first I tried explain, which rather quickly knows how many rows
to check, but the final count is two orders of magnitude slower.
My MS_SQL server using colleague can't believe that.
$ psql InfluenzaWeb -c 'explain SELECT
* Andreas Tille [EMAIL PROTECTED] [070322 12:07]:
Hi,
I just try to find out why a simple count(*) might last that long.
At first I tried explain, which rather quickly knows how many rows
to check, but the final count is two orders of magnitude slower.
Which version of PG?
The basic
As you can see, PostgreSQL needs to do a sequencial scan to count because its
MVCC nature and indices don't have transaction information. It's a known
drawback inherent to the way PostgreSQL works and which gives very good
results in other areas. It's been talked about adding some kind of
explain is just quessing how many rows are in table. sometimes quess is
right, sometimes just an estimate.
sailabdb=# explain SELECT count(*) from sl_tuote;
QUERY PLAN
approximated count?
why? who would need it? where you can use it?
calculating costs and desiding how to execute query needs
approximated count, but it's totally worthless information for any user
IMO.
Ismo
On Thu, 22 Mar 2007, Albert Cervera Areny wrote:
As you can see, PostgreSQL
On Thu, 22 Mar 2007, Andreas Kostyrka wrote:
Which version of PG?
Ahh, sorry, forgot that. The issue occurs in Debian (Etch) packaged
version 7.4.16. I plan to switch soon to 8.1.8.
That's the reason why PG (check the newest releases, I seem to
remember that there has been some aggregate
* Andreas Tille [EMAIL PROTECTED] [070322 13:24]:
On Thu, 22 Mar 2007, Andreas Kostyrka wrote:
Which version of PG?
Ahh, sorry, forgot that. The issue occurs in Debian (Etch) packaged
version 7.4.16. I plan to switch soon to 8.1.8.
I'd recommend 8.2 if at all possible :)
That's the
In response to [EMAIL PROTECTED]:
approximated count?
why? who would need it? where you can use it?
calculating costs and desiding how to execute query needs
approximated count, but it's totally worthless information for any user
IMO.
I don't think so.
We have some AJAX stuff
On 3/22/07, Andreas Tille [EMAIL PROTECTED] wrote:
I just try to find out why a simple count(*) might last that long.
At first I tried explain, which rather quickly knows how many rows
to check, but the final count is two orders of magnitude slower.
You can get the approximate count by
On Thu, Mar 22, 2007 at 01:30:35PM +0200, [EMAIL PROTECTED] wrote:
approximated count?
why? who would need it? where you can use it?
Do a google query. Look at the top of the page, where it says
results N to M of about O. For user interfaces (which is where a lot
of this count(*) stuff
On 3/22/07, Merlin Moncure [EMAIL PROTECTED] wrote:
As others suggest select count(*) from table is very special case
which non-mvcc databases can optimize for.
Well, other MVCC database still do it faster than we do. However, I
think we'll be able to use the dead space map for speeding this
Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
On 3/22/07, Merlin Moncure [EMAIL PROTECTED] wrote:
As others suggest select count(*) from table is very special case
which non-mvcc databases can optimize for.
Well, other MVCC database still do it faster than we do. However, I
On Thu, Mar 22, 2007 at 01:29:46PM +0100, Andreas Kostyrka wrote:
* Andreas Tille [EMAIL PROTECTED] [070322 13:24]:
Well, to be honest I'm not really interested in the performance of
count(*). I was just discussing general performance issues on the
phone line and when my colleague asked me
On Thu, Mar 22, 2007 at 09:39:18AM -0400, Merlin Moncure wrote:
You can get the approximate count by selecting reltuples from
pg_class. It is valid as of last analyze.
Of course, that only works if you're not using any WHERE clause.
Here's a (somewhat ugly) example of getting an approximate
On Thu, Mar 22, 2007 at 10:18:10AM -0400, Michael Stone wrote:
IIRC, that's basically what you get with the mysql count anyway, since
there are corner cases for results in a transaction. Avoiding those
cases is why the postgres count takes so long; sometimes that's what's
desired and
Andreas,
On 3/22/07 4:48 AM, Andreas Tille [EMAIL PROTECTED] wrote:
Well, to be honest I'm not really interested in the performance of
count(*). I was just discussing general performance issues on the
phone line and when my colleague asked me about the size of the
database he just wonderd
* Mario Weilguni [EMAIL PROTECTED] [070322 15:59]:
Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
On 3/22/07, Merlin Moncure [EMAIL PROTECTED] wrote:
As others suggest select count(*) from table is very special case
which non-mvcc databases can optimize for.
Well, other
Michael Stone wrote:
On Thu, Mar 22, 2007 at 01:30:35PM +0200, [EMAIL PROTECTED] wrote:
approximated count?
why? who would need it? where you can use it?
Do a google query. Look at the top of the page, where it says results N
to M of about O. For user interfaces (which is where a lot of
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we
actually can't use Postgres indexing at all -- we wrote an entirely
separate indexing system for our data, one that has the following
properties:
1. It can give out pages of information (i.e.
count(*). I was just discussing general performance issues on the
phone line and when my colleague asked me about the size of the
database he just wonderd why this takes so long for a job his
MS-SQL server is much faster. [...].
Simple. MSSQL is optimized for this case, and uses older
Craig A. James wrote:
One of our biggest single problems is this very thing. It's not a
Postgres problem specifically, but more embedded in the idea of a
relational database: There are no job status or rough estimate of
results or give me part of the answer features that are critical to
Am Donnerstag, 22. März 2007 16:17 schrieb Andreas Kostyrka:
* Mario Weilguni [EMAIL PROTECTED] [070322 15:59]:
Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
On 3/22/07, Merlin Moncure [EMAIL PROTECTED] wrote:
As others suggest select count(*) from table is very special
Brian Hurt wrote:
One of our biggest single problems is this very thing. It's not a
Postgres problem specifically, but more embedded in the idea of a
relational database: There are no job status or rough estimate of
results or give me part of the answer features that are critical to
many
Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we
actually can't use Postgres indexing at all -- we wrote an entirely
separate indexing system for our data...
...There is no need to store or
maintain this information along
Craig A. James schrieb:
Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we
actually can't use Postgres indexing at all -- we wrote an entirely
separate indexing system for our data...
...There is no need to store or
On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote:
Craig A. James schrieb:
You guys can correct me if I'm wrong, but the key feature that's missing
from Postgres's flexible indexing is the ability to maintain state
across queries. Something like this:
select a, b,
On Mar 22, 2007, at 10:21 AM, Craig A. James wrote:
Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical),
we actually can't use Postgres indexing at all -- we wrote an
entirely separate indexing system for our data...
On 3/22/07, Michael Stone [EMAIL PROTECTED] wrote:
On Thu, Mar 22, 2007 at 06:27:32PM +0100, Tino Wildenhain wrote:
Craig A. James schrieb:
You guys can correct me if I'm wrong, but the key feature that's missing
from Postgres's flexible indexing is the ability to maintain state
across queries.
On 22.03.2007, at 11:53, Steve Atkins wrote:
As long as you're ordering by some row in the table then you can do
that in
straight SQL.
select a, b, ts from foo where (stuff) and foo X order by foo
limit 10
Then, record the last value of foo you read, and plug it in as X
the next
time
Craig A. James [EMAIL PROTECTED] writes:
Steve Atkins wrote:
As long as you're ordering by some row in the table then you can do that in
straight SQL.
select a, b, ts from foo where (stuff) and foo X order by foo limit 10
Then, record the last value of foo you read, and plug it in as X
On Thu, Mar 22, 2007 at 02:24:39PM -0400, Merlin Moncure wrote:
Tino was saying that rather that build a complete indexing storage
management solution that lives outside the database, it is better to
do intelligent session management so that you get the simplicity if a
two tier client server
Tom Lane wrote:
Craig A. James [EMAIL PROTECTED] writes:
Steve Atkins wrote:
As long as you're ordering by some row in the table then you can do that in
straight SQL.
select a, b, ts from foo where (stuff) and foo X order by foo limit 10
Then, record the last value of foo you read, and plug
Michael Stone schrieb:
On Thu, Mar 22, 2007 at 02:24:39PM -0400, Merlin Moncure wrote:
Tino was saying that rather that build a complete indexing storage
management solution that lives outside the database, it is better to
do intelligent session management so that you get the simplicity if a
Craig A. James schrieb:
Tino Wildenhain wrote:
You guys can correct me if I'm wrong, but the key feature that's
missing from Postgres's flexible indexing is the ability to maintain
state across queries. Something like this:
select a, b, my_index_state() from foo where ...
offset 100
Doing some rather crude comparative performance tests
between PG 8.0.1 on Windows XP and SQL Server 2000, PG
whips SQL Server's ass on
insert into junk (select * from junk)
on a one column table defined as int.
If we start with a 1 row table and repeatedly execute
this command, PG can take the
38 matches
Mail list logo
