SEO

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 count(*) from agiraw ;'
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=196969.77..196969.77 rows=1 width=0)
-> Seq Scan on agiraw (cost=0.00..185197.41 rows=4708941 width=0)
(2 rows)

real 0m0.066s
user 0m0.024s
sys 0m0.008s

$ psql InfluenzaWeb -c 'SELECT count(*) from agiraw ;'
count
---------
4708941
(1 row)

real 0m4.474s
user 0m0.036s
sys 0m0.004s


Any explanation?

Kind regards

Andreas.

--
http://fam-tille.de

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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. "rows 50-60") without
> rescanning the skipped pages the way "limit/offset" would.
> 2. It can give accurate estimates of the total rows that will be returned.
> 3. It can accurately estimate the time it will take.
>

Thats certainly not entirely correct. There is no need to store or
maintain this information along with postgres when you can store
and maintain it directly in postgres as well. When you have some
outside application I think I can savely assume you are doing
less updates compared to many reads to have it actually pay out.

So why not store this information in separate "index" and "statistic"
tables? You would have just to join with your real data for retrival.

On top of that, postgres has a very flexible and extensible index
system. This would mean you save on database roundtrips and
double information storage (and the sync problems you certainly
get from it)

Regards
Tino


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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 count based
off the statistics info, which will work for more complicated queries:
http://archives.postgresql.org/pgsql...8/msg00046.php
The ugliness is that you have to provide the whole query as a
parameter to the function, instead of using it as a drop-in replacement
for count. I assume that the TODO item is to provide the latter, but for
now this method can be useful for UI type stuff where you just want to
know whether there's "a little" or "a lot".

Mike Stone

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 3/22/07, Merlin Moncure <mmoncure@gmail.com> 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 up a
bit wouldn't we?

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Am Donnerstag, 22. März 2007 16:17 schrieb Andreas Kostyrka:
> * Mario Weilguni <mweilguni@sime.com> [070322 15:59]:
> > Am Donnerstag, 22. März 2007 15:33 schrieb Jonah H. Harris:
> > > On 3/22/07, Merlin Moncure <mmoncure@gmail.com> 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 up a
> > > bit wouldn't we?
> >
> > Which MVCC DB do you mean? Just curious...
>
> Well, mysql claims InnoDB to be mvcc

Ok, but last time I tried count(*) with InnoDB tables did take roughly(*) the
same time last time I tried - because InnoDB has the same problem as postgres
and has to do a seqscan too (I think it's mentioned somewhere in their docs).

(*) in fact, postgres was faster, but the values were comparable, 40 seconds
vs. 48 seconds

Maybe the InnoDB have made some progress here, I tested it with MySQL 5.0.18.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Am Donnerstag, 22. März 2007 12:30 schrieb ismo.tuononen@solenovo.fi:
> 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.

No, it is not useless. Try:
http://www.google.com/search?hl=de&q...le-Suche&meta=

Do you really think google counted each of those individual 895 million
results? It doesn't. In fact, the estimate of google can be off by an order
of magnitude, and still nobody complains...


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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 sometimes it is not.

Adding to this point:

In any production system, the count presented to the user is usually
wrong very shortly after it is displayed anyways. Transactions in the
background or from other users are adding or removing items, perhaps
even before the count reaches the user's display.

The idea of transaction-safety for counts doesn't apply in this case.
Both the transaction and the number are complete before the value is
displayed.

In my own systems, I rarely use count(*) for anything except user
visible results. For the PostgreSQL system I use, I keep a table of
counts, and lock the row for update when adding or removing items.
This turns out to be best in this system anyways, as I need my new
rows to be ordered, and locking the 'count' row lets me assign a
new sequence number for the row. (Don't want to use SEQUENCE objects,
as there could as the rows are [key, sequence, data], with thousands
or more keys)

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
.. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

>>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"
>datastructures. PG uses a MVCC storage,
>

Which version of MSSQL? Wikipedia says that SQL Server 2005 uses the
MVCC model.

Carlos
--


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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 real applications.
>>
> For the "give me part of the answer", I'm wondering if cursors wouldn't
> work (and if not, why not)?

There is no mechanism in Postgres (or any RDB that I know of) to say, "Give me rows 1000 through 1010", that doesn't also execute the query on rows 1-1000. In other words, the RDBMS does the work for 1010 rows, when only 10 are needed -- 100 times more work than is necessary.

Limit/Offset will return the correct 10 rows, but at the cost of doing the previous 1000 rows and discarding them.

Web applications are stateless. To use a cursor, you'd have to keep it around for hours or days, and create complex "server affinity" code to direct a user back to the same server of your server farm (where that cursor is being held), on the chance that the user will come back and ask for rows 1000 through 1010, then a cursor isn't up to the task.

Craig

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 3/22/07, Andreas Tille <tillea@rki.de> 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 selecting reltuples from
pg_class. It is valid as of last analyze.

As others suggest select count(*) from table is very special case
which non-mvcc databases can optimize for. There are many reasons why
this is the case and why it explains nothing about the relative
performance of the two databases. This is probably #1 most
frequenctly asked question to -performance...there is a wealth of
information in the archives.

merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match