Re: Newbie help with performance on a few queries

#1

begin Poochie© schrieb:

Quote:

Hi all,

I am quite new to PGSql and I used to try out MySQL. What I often need
is to know the number of records of every table or the max value of a

Do you need really this? A "select count(*) from table" forces an seq.
scan, very slow. You can ask the table pg_class for an estimation.

Btw.: there an many discussion about this in the past, for instance
http://groups.google.de/group/mailin...6b018b2a1ae372

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#2

Poochie© <poochie{digit:=2}@{googlemail}.com> wrote:

Quote:

I am quite new to PGSql and I used to try out MySQL. What I often need
is to know the number of records of every table or the max value of a
peculiarfield (usually a date) of every one of the tables in my schema.

In MySQL I found that a simple:

SELECT myUnion.tab_name, myUnion.cnt AS "count" FROM (
(SELECT "t01" AS tab_name, count(*) FROM t01 WHERE boolField = 0)
UNION
( ... )
UNION
( ... )
...
[about 50 tables]
...
) AS myUnion;

.. was quite ok, being the first call quite fast (200 ms?) and the
followingones almost instantaneous thanks to the caching of queries.

Do you mean "caching of queries" or "caching of query results"?
The former would not help you at all in this case, and the latter would
assume that none of the tables is touched inbetween (else the result
would have to be calculated again).

Quote:

In PG I found that the same query performs very poorly (same DB
converted via mysql2pgsql.perl) on the same computer (A64 X2 2 GHz, 2
GB RAM) taking about 1500 ms each time (with a decent amount of HD
loading activity evenrepeating the same query again and again...).

My questions are:

- Is there a smarter way to make a similar query that performs quite
faster? - Is it possible to activate some kind of query caching or
better usage of the system memory to reduce HD usage (my DB is quite
small, no more than 10.000 records in the heaviest tables and many are
sub 20)?

The query is of course a horror and you can only get away with it because
your tables are small.

There is no query result caching in PostgreSQL.

Do these tables change regularly?
If yes, you'll get a performance improvement by doing a VACUUM FULL
regularly, because that will compact the tables and avoid unnecessary
disk I/O.

If you do not need the exact numbers, querying relclass is an alternative,
as has been suggested in another reply:

SELECT reltuples
FROM pg_class t JOIN
pg_namespace n ON (t.relnamespace = n.oid)
WHERE nspname='schemaname' AND relname='tablename';

This also assumes that you VACUUM regularly.

Yours,
Laurenz Albe

#3

Quote:

I am quite new to PGSql and I used to try out MySQL. What I often
need is to know the number of records of every table or the max
value of a peculiarfield (usually a date) of every one of the tables
in my schema.
In MySQL I found that a simple:

SELECT myUnion.tab_name, myUnion.cnt AS "count" FROM (
(SELECT "t01" AS tab_name, count(*) FROM t01 WHERE
boolField = 0) UNION
( ... )
UNION
( ... )
...
[about 50 tables]
...
) AS myUnion;

.. was quite ok, being the first call quite fast (200 ms?) and the
followingones almost instantaneous thanks to the caching of queries.

Do you mean "caching of queries" or "caching of query results"?

Obviously the second one, or at least that's how I think MySQL cache
works. The fact is that there all my operations are seamless, just
caching the tables in the RAM should give a notable performance boost
to pgsql, wouldn't it?

You wield strange terms :^) What is a seamless operation?

I think you are referring to the shared buffer pool. PostgreSQL, like
every database I've ever seen, will cache database blocks in shared
memory.

Which brings me to another idea: Maybe you can improve your performance if
you give your database server more shared memory (parameter shared_buffers)
so that your whole database can be cached in memory.

Yours,
Laurenz Albe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Newbie help with performance on a few queries

comp.databases.postgresql comp.databases.postgresql

Re: Newbie help with performance on a few queries - 06-22-2007 , 12:57 AM

Re: Newbie help with performance on a few queries - 06-22-2007 , 06:09 AM

Re: Newbie help with performance on a few queries - 06-25-2007 , 02:32 AM