Thread

getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T00:40:13Z

I have implemented this TODO item:

	* Add getpid() function to backend

There were a large number of pg_stat functions that access pids and
backends slots so I added it there:
	
	test=> select pg_stat_get_backend_mypid();
	 pg_stat_get_backend_mypid 
	---------------------------
	                      2757
	(1 row)

Applied.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Neil Conway <nconway@klamath.dyndns.org> — 2002-07-31T01:17:58Z

On Tue, Jul 30, 2002 at 08:40:13PM -0400, Bruce Momjian wrote:
> I have implemented this TODO item:
> 
> 	* Add getpid() function to backend
> 
> There were a large number of pg_stat functions that access pids and
> backends slots so I added it there:
> 	
> 	test=> select pg_stat_get_backend_mypid();

If we're going to add it to pg_stat_*, why is 'backend' part of the
name? All the existing backend_* function fetch some piece of data
about a given backend -- whereas this function does not (it takes
no arguments).

IMHO, a better name would be something like 'backend_process_id()',
or 'unix_pid', or 'backend_pid()'.

Also, can you add some documentation on this?

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T01:48:42Z

OK, renamed to backend_pid() to match the libpq name.  I was unsure
about merging it into the stats stuff myself.
	
	setest=> select backend_pid();
	 backend_pid 
	-------------
	       12996
	(1 row)

Where does the mention belong in the docs? I have it in the monitoring
section in the stats section right now.

---------------------------------------------------------------------------

Neil Conway wrote:
> On Tue, Jul 30, 2002 at 08:40:13PM -0400, Bruce Momjian wrote:
> > I have implemented this TODO item:
> > 
> > 	* Add getpid() function to backend
> > 
> > There were a large number of pg_stat functions that access pids and
> > backends slots so I added it there:
> > 	
> > 	test=> select pg_stat_get_backend_mypid();
> 
> If we're going to add it to pg_stat_*, why is 'backend' part of the
> name? All the existing backend_* function fetch some piece of data
> about a given backend -- whereas this function does not (it takes
> no arguments).
> 
> IMHO, a better name would be something like 'backend_process_id()',
> or 'unix_pid', or 'backend_pid()'.
> 
> Also, can you add some documentation on this?
> 
> Cheers,
> 
> Neil
> 
> -- 
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Neil Conway <nconway@klamath.dyndns.org> — 2002-07-31T01:59:29Z

On Tue, Jul 30, 2002 at 09:48:42PM -0400, Bruce Momjian wrote:
> OK, renamed to backend_pid() to match the libpq name.

Ok, thanks.

> Where does the mention belong in the docs? I have it in the monitoring
> section in the stats section right now.

I'd vote for User's Guide -> Functions & Operators -> Misc. Functions. I
don't think it belongs in the monitoring section, since it isn't part of
the stats collector and isn't really used for monitoring.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T02:43:07Z

Neil Conway wrote:
> On Tue, Jul 30, 2002 at 09:48:42PM -0400, Bruce Momjian wrote:
> > OK, renamed to backend_pid() to match the libpq name.
> 
> Ok, thanks.
> 
> > Where does the mention belong in the docs? I have it in the monitoring
> > section in the stats section right now.
> 
> I'd vote for User's Guide -> Functions & Operators -> Misc. Functions. I
> don't think it belongs in the monitoring section, since it isn't part of
> the stats collector and isn't really used for monitoring.

OK, docs moved to new section.  I kept the function in pgstatfuncs.c. 
Not sure where else to put it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-31T03:29:12Z

nconway@klamath.dyndns.org (Neil Conway) writes:
> On Tue, Jul 30, 2002 at 09:48:42PM -0400, Bruce Momjian wrote:
>> Where does the mention belong in the docs? I have it in the monitoring
>> section in the stats section right now.

> I'd vote for User's Guide -> Functions & Operators -> Misc. Functions.

There is a table in that section for "session information functions",
which seems the correct choice.

			regards, tom lane

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T03:30:16Z

Tom Lane wrote:
> nconway@klamath.dyndns.org (Neil Conway) writes:
> > On Tue, Jul 30, 2002 at 09:48:42PM -0400, Bruce Momjian wrote:
> >> Where does the mention belong in the docs? I have it in the monitoring
> >> section in the stats section right now.
> 
> > I'd vote for User's Guide -> Functions & Operators -> Misc. Functions.
> 
> There is a table in that section for "session information functions",
> which seems the correct choice.

That's where I put it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Karel Zak <zakkr@zf.jcu.cz> — 2002-08-01T10:01:52Z

On Tue, Jul 30, 2002 at 09:48:42PM -0400, Bruce Momjian wrote:
> 
> OK, renamed to backend_pid() to match the libpq name.  I was unsure
> about merging it into the stats stuff myself.
> 	
> 	setest=> select backend_pid();
> 	 backend_pid 
> 	-------------
> 	       12996
> 	(1 row)

 Is there some common convention of names? Why not pg_backend_pid()?

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: getpid() function

Neil Conway <nconway@klamath.dyndns.org> — 2002-08-01T14:44:23Z

On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
>  Is there some common convention of names?

No, there isn't (for example, pg_stat_backend_id() versus
current_schema() -- or pg_get_viewdef() versus obj_description() ).
Now that we have table functions, we might be using more built-in
functions to provide information to the user -- so there will be
an increasing need for some kind of naming convention for built-in
functions. However, establishing a naming convention without
breaking backwards compatibility might be tricky.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: getpid() function

Rod Taylor <rbt@zort.ca> — 2002-08-01T14:48:32Z

On Thu, 2002-08-01 at 10:44, Neil Conway wrote:
> On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
> >  Is there some common convention of names?

> functions. However, establishing a naming convention without
> breaking backwards compatibility might be tricky.

Supporting both names for a release with comments in the release notes
stating the old names will disappear soon should be enough.

Most of the time it'll be a simple replacement command.  Providing a
find -exec sed statement may help.

Re: getpid() function

Karel Zak <zakkr@zf.jcu.cz> — 2002-08-01T15:09:52Z

On Thu, Aug 01, 2002 at 10:44:23AM -0400, Neil Conway wrote:
> On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
> >  Is there some common convention of names?
> 
> No, there isn't (for example, pg_stat_backend_id() versus

 I know -- for this I asked. IMHO for large project like PostgreSQL
 it's important. It's not good if there is possible speculate about
 name of new function. It must be unmistakable -- for this is needful
 make some convension. If somebody add new function and it's released,
 it's in the PostgreSQL almost forever.

> current_schema() -- or pg_get_viewdef() versus obj_description() ).
> Now that we have table functions, we might be using more built-in
> functions to provide information to the user -- so there will be
> an increasing need for some kind of naming convention for built-in
> functions. However, establishing a naming convention without
> breaking backwards compatibility might be tricky.

 Yes, but we can try be clean for new stuff.

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T17:41:49Z

Added to TODO:

	* Consistently name server-side internal functions

---------------------------------------------------------------------------

Karel Zak wrote:
> On Thu, Aug 01, 2002 at 10:44:23AM -0400, Neil Conway wrote:
> > On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
> > >  Is there some common convention of names?
> > 
> > No, there isn't (for example, pg_stat_backend_id() versus
> 
>  I know -- for this I asked. IMHO for large project like PostgreSQL
>  it's important. It's not good if there is possible speculate about
>  name of new function. It must be unmistakable -- for this is needful
>  make some convension. If somebody add new function and it's released,
>  it's in the PostgreSQL almost forever.
> 
> > current_schema() -- or pg_get_viewdef() versus obj_description() ).
> > Now that we have table functions, we might be using more built-in
> > functions to provide information to the user -- so there will be
> > an increasing need for some kind of naming convention for built-in
> > functions. However, establishing a naming convention without
> > breaking backwards compatibility might be tricky.
>  
>  Yes, but we can try be clean for new stuff.
>  
>     Karel
> 
> -- 
>  Karel Zak  <zakkr@zf.jcu.cz>
>  http://home.zf.jcu.cz/~zakkr/
>  
>  C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T17:42:47Z

I can rename backend_pid if people want.   I just made it consistent
with the other functions in that docs area.  Comments?

---------------------------------------------------------------------------

Karel Zak wrote:
> On Thu, Aug 01, 2002 at 10:44:23AM -0400, Neil Conway wrote:
> > On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
> > >  Is there some common convention of names?
> > 
> > No, there isn't (for example, pg_stat_backend_id() versus
> 
>  I know -- for this I asked. IMHO for large project like PostgreSQL
>  it's important. It's not good if there is possible speculate about
>  name of new function. It must be unmistakable -- for this is needful
>  make some convension. If somebody add new function and it's released,
>  it's in the PostgreSQL almost forever.
> 
> > current_schema() -- or pg_get_viewdef() versus obj_description() ).
> > Now that we have table functions, we might be using more built-in
> > functions to provide information to the user -- so there will be
> > an increasing need for some kind of naming convention for built-in
> > functions. However, establishing a naming convention without
> > breaking backwards compatibility might be tricky.
>  
>  Yes, but we can try be clean for new stuff.
>  
>     Karel
> 
> -- 
>  Karel Zak  <zakkr@zf.jcu.cz>
>  http://home.zf.jcu.cz/~zakkr/
>  
>  C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Hannu Krosing <hannu@tm.ee> — 2002-08-01T18:50:36Z

On Thu, 2002-08-01 at 19:41, Bruce Momjian wrote:
> 
> Added to TODO:
> 
> 	* Consistently name server-side internal functions

I'd suggest:

  * Make up rules for consistently naming server-side internal functions

  * Consistently name _new_ server-side internal functions

  * make a plan for moving existing server-side internal functions
    to consistent naming

---------------
Hannu

Re: getpid() function

Neil Conway <nconway@klamath.dyndns.org> — 2002-08-01T19:09:25Z

On Thu, Aug 01, 2002 at 05:09:52PM +0200, Karel Zak wrote:
>  I know -- for this I asked. IMHO for large project like PostgreSQL
>  it's important. It's not good if there is possible speculate about
>  name of new function. It must be unmistakable -- for this is needful
>  make some convension. If somebody add new function and it's released,
>  it's in the PostgreSQL almost forever.

I agree that a naming convention would be useful in some circumstances,
but for commonly-used functions, I think it would do more harm than
good. 'pg_nextval()' is awfully ugly, for example.

And if we're going to have a naming convention for builtin functions,
what about builtin types? 'pg_int4', anyone? :-)

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: getpid() function

Peter Eisentraut <peter_e@gmx.net> — 2002-08-01T20:05:11Z

Neil Conway writes:

> On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
> >  Is there some common convention of names?
>
> No, there isn't (for example, pg_stat_backend_id() versus
> current_schema() -- or pg_get_viewdef() versus obj_description() ).

The "pg_" naming scheme is obsolete because system and user namespaces are
now isolated.  Anything involving "get" is also redundant, IMHO, because
we aren't dealing with object-oriented things.  Besides that, the
convention in SQL seems to be to use full noun phrases with words
separated by underscores.

So if "pg_get_viewdef" where reinvented today, by me, it would be called
"view_definition".

A whole 'nother issue is to use the right terms for the right things.  For
example, the term "backend" is rather ambiguous and PostgreSQL uses it
differently from everyone else.  Instead I would use "server process" when
referring to the PID.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T21:02:11Z

Peter Eisentraut wrote:
> Neil Conway writes:
> 
> > On Thu, Aug 01, 2002 at 12:01:52PM +0200, Karel Zak wrote:
> > >  Is there some common convention of names?
> >
> > No, there isn't (for example, pg_stat_backend_id() versus
> > current_schema() -- or pg_get_viewdef() versus obj_description() ).
> 
> The "pg_" naming scheme is obsolete because system and user namespaces are
> now isolated.  Anything involving "get" is also redundant, IMHO, because
> we aren't dealing with object-oriented things.  Besides that, the
> convention in SQL seems to be to use full noun phrases with words
> separated by underscores.
> 
> So if "pg_get_viewdef" where reinvented today, by me, it would be called
> "view_definition".
> 
> A whole 'nother issue is to use the right terms for the right things.  For
> example, the term "backend" is rather ambiguous and PostgreSQL uses it
> differently from everyone else.  Instead I would use "server process" when
> referring to the PID.

Yes, I wanted to match libpq's function, which is the way people used to
get the pid.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-02T01:13:06Z

> No, there isn't (for example, pg_stat_backend_id() versus
> current_schema() -- or pg_get_viewdef() versus obj_description() ).
> Now that we have table functions, we might be using more built-in
> functions to provide information to the user -- so there will be
> an increasing need for some kind of naming convention for built-in
> functions. However, establishing a naming convention without
> breaking backwards compatibility might be tricky.

I personally think that as many functions as possible should be prefixed
pg_*...  People are still used to avoiding pg_ as a prefix.

Chris

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-02T04:23:25Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can rename backend_pid if people want.   I just made it consistent
> with the other functions in that docs area.  Comments?

I'd go for pg_backend_pid, I think.  It's not an SQL standard function
and certainly never will be, so some sort of prefix seems appropriate.

Perhaps a more relevant question is why are we cluttering the namespace
with any such function at all?  What's the use case for it?  We've
gotten along fine without one so far, and I don't really think that we
*ought* to be exposing random bits of internal implementation details
at the SQL level.

			regards, tom lane

Re: getpid() function

lockhart@fourpalms.org — 2002-08-02T04:48:34Z

...
> Perhaps a more relevant question is why are we cluttering the namespace
> with any such function at all?  What's the use case for it?  We've
> gotten along fine without one so far, and I don't really think that we
> *ought* to be exposing random bits of internal implementation details
> at the SQL level.

Actually, I was wondering the same thing, maybe for a different reason.
Exposing the backend internals could have security implications (though
don't make me concoct a scenario to prove it ;)

Although it might have some usefulness for debugging, I think it should
not be an "installed by default" feature, so istm would be a great
candidate for a contrib/ function or library. If someone needs it, it is
almost immediately available.

                  - Thomas

Re: getpid() function

Karel Zak <zakkr@zf.jcu.cz> — 2002-08-02T08:26:36Z

On Thu, Aug 01, 2002 at 01:41:49PM -0400, Bruce Momjian wrote:
> 
> Added to TODO:
> 
> 	* Consistently name server-side internal functions

 OK, good start of discussion is define groups of the PostgreSQL 
 functions:

 1/ Extern compatible functions

     The functions compatible with standards or customs
     or others SQL servers. For example trim, to_char, ...
     
 2/ PostgreSQL specific functions used in standard SQL operations
 
    (the function works with standard data and not load it from 
    internal PostgreSQL stuff).

    For example convert(), all datetype function like int(). The name 
    convenition must be like names in group 1/

 3/ PostgreSQL specific system functions

    For example pg_backend_pid(). IMHO clean solution is
    use "pg_" prefix.

 4/ The calls without '( )'

    For example "SELECT current_user;" IMHO right is not use
    "pg_" prefix _if_ you call it without braces. _But_ if you call
    it with '()' and function can be member of group 3/ is right use
    "pg_" prefix.

    For example:
        SELECT current_user;
        SELECT pg_current_user();

 5/ Deprecated functions

    In docs marked as "deprecated" and will removed in some major
    release (for example in 8.0).


 6/ ???

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: getpid() function

Tatsuo Ishii <t-ishii@sra.co.jp> — 2002-08-02T08:38:37Z

>  2/ PostgreSQL specific functions used in standard SQL operations
>  
>     (the function works with standard data and not load it from 
>     internal PostgreSQL stuff).
> 
>     For example convert(), all datetype function like int(). The name 
>     convenition must be like names in group 1/

FYI, I have been proposing SQL99 compatible convert(). I would like to
add it if no one objects.
--
Tatsuo Ishii

Re: getpid() function

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-02T08:50:13Z

> >     For example convert(), all datetype function like int(). The name
> >     convenition must be like names in group 1/
>
> FYI, I have been proposing SQL99 compatible convert(). I would like to
> add it if no one objects.

No objection, but what does it do out of interest?  Will it cause a
backwards compatibility problem at all?

Chris

[]performance issues

Yaroslav Dmitriev <yar@warlock.ru> — 2002-08-02T08:59:19Z

Hello,

Sorry if it's wrong list for the question. Could you suggest some tweaks 
to the PostgreSQL 7.2.1 to handle the following types of tables faster?

Here we have  table "stats" with  something over one millon records. 
Obvious "SELECT COUNT(*) FROM stats " takes over 40 seconds to execute, 
and this amount of time does not shorten considerably in subsequent 
similar requests. All the databases are vacuumed nightly.

CREATE TABLE "stats" (
   "url" varchar(50),
   "src_port" varchar(10),
   "ip" varchar(16),
   "dst_port" varchar(10),
   "proto" varchar(10),
   "size" int8,
   "login" varchar(20),
   "start_date" timestamptz,
   "end_date" timestamptz,
   "aggregated" int4
);
CREATE  INDEX "aggregated_stats_key" ON "stats" ("aggregated");
CREATE  INDEX "ip_stats_key" ON "stats" ("ip");

stats=> explain select count(*) from stats;
NOTICE:  QUERY PLAN:

Aggregate  (cost=113331.10..113331.10 rows=1 width=0)
  ->  Seq Scan on stats  (cost=0.00..110085.28 rows=1298328 width=0)

EXPLAIN
stats=> select count(*) from stats;
  count  
---------
 1298328
(1 row)

The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512 Mb DDR, ATA 100 HDD.

Thanks in advance,
Yar

Re: []performance issues

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-02T09:15:38Z

> Here we have  table "stats" with  something over one millon records.
> Obvious "SELECT COUNT(*) FROM stats " takes over 40 seconds to execute,
> and this amount of time does not shorten considerably in subsequent
> similar requests. All the databases are vacuumed nightly.

Doing a row count requires a sequential scan in Postgres.

Try creating another summary table that just has one row and one column and
is an integer.

Then, create a trigger on your stats table that fires whenever a new row is
added or deleted and updates the tally of rows in the summary table.

Then, just select from the summary table to get an instantaneous count.  Of
course, insert and deletes will be marginally slowed down.

Refer to the docs for CREATE TRIGGER, CREATE FUNCTION and PL/PGSQL for more
info on how to do this.

Regards,

Chris

Re: getpid() function

Karel Zak <zakkr@zf.jcu.cz> — 2002-08-02T09:51:39Z

On Fri, Aug 02, 2002 at 05:38:37PM +0900, Tatsuo Ishii wrote:
> >  2/ PostgreSQL specific functions used in standard SQL operations
> >  
> >     (the function works with standard data and not load it from 
> >     internal PostgreSQL stuff).
> > 
> >     For example convert(), all datetype function like int(). The name 
> >     convenition must be like names in group 1/
> 
> FYI, I have been proposing SQL99 compatible convert(). I would like to
> add it if no one objects.

 I use convert() as example only. I think there is more function
 for group 2/.

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: []performance issues

Yaroslav Dmitriev <yar@warlock.ru> — 2002-08-02T11:48:39Z

Christopher Kings-Lynne wrote:

>Doing a row count requires a sequential scan in Postgres.
>
>Try creating another summary table that just has one row and one column and
>is an integer.
>  
>

I have THREE  summary tables derived from "stats" with different levels 
of aggregation. They work quite fast,  But:

1) Summary tables grow too
2) There are requests which cannot be predicted, so they involve the 
"stats" table itself.

So I am still interested in PostgreSQL's ability to deal with 
multimillon records tables.

Best regards,
Yar.

Re: []performance issues

Sergio A. Kessler <sak@ksb.com.ar> — 2002-08-02T13:38:03Z

times change if you do
"SELECT COUNT(1) FROM stats" ?

--
:: Sergio A. Kessler ::
Linux user #64005 - http://counter.li.org

"Yaroslav Dmitriev" <yar@warlock.ru> escribi en el mensaje
news:3D4A49E7.6090405@warlock.ru...
> Hello,
>
> Sorry if it's wrong list for the question. Could you suggest some tweaks
> to the PostgreSQL 7.2.1 to handle the following types of tables faster?
>
> Here we have  table "stats" with  something over one millon records.
> Obvious "SELECT COUNT(*) FROM stats " takes over 40 seconds to execute,
> and this amount of time does not shorten considerably in subsequent
> similar requests. All the databases are vacuumed nightly.
>
> CREATE TABLE "stats" (
>    "url" varchar(50),
>    "src_port" varchar(10),
>    "ip" varchar(16),
>    "dst_port" varchar(10),
>    "proto" varchar(10),
>    "size" int8,
>    "login" varchar(20),
>    "start_date" timestamptz,
>    "end_date" timestamptz,
>    "aggregated" int4
> );
> CREATE  INDEX "aggregated_stats_key" ON "stats" ("aggregated");
> CREATE  INDEX "ip_stats_key" ON "stats" ("ip");
>
> stats=> explain select count(*) from stats;
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=113331.10..113331.10 rows=1 width=0)
>   ->  Seq Scan on stats  (cost=0.00..110085.28 rows=1298328 width=0)
>
> EXPLAIN
> stats=> select count(*) from stats;
>   count
> ---------
>  1298328
> (1 row)
>
> The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512 Mb
DDR, ATA 100 HDD.
>
> Thanks in advance,
> Yar
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: [HACKERS] []performance issues

Andrew Sullivan <andrew@libertyrms.info> — 2002-08-02T15:39:32Z

On Fri, Aug 02, 2002 at 03:48:39PM +0400, Yaroslav Dmitriev wrote:
> 
> So I am still interested in PostgreSQL's ability to deal with 
> multimillon records tables.

[x-posted and Reply-To: to -general; this isn't a development
problem.]

We have tables with multimillion records, and they are fast.  But not
fast to count().  The MVCC design of PostgreSQL will give you very
few concurerncy problems, but you pay for that in the response time
of certain kinds of aggregates, which cannot use an index.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: [HACKERS] []performance issues

David Blood <david@matraex.com> — 2002-08-02T15:57:16Z

Count() is slow even on your Sun server with 16gb ram?  How big is the
database?

David Blood
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Friday, August 02, 2002 9:40 AM
To: PostgreSQL-development
Cc: PostgreSQL general list
Subject: Re: [GENERAL] [HACKERS] []performance issues

On Fri, Aug 02, 2002 at 03:48:39PM +0400, Yaroslav Dmitriev wrote:
> 
> So I am still interested in PostgreSQL's ability to deal with 
> multimillon records tables.

[x-posted and Reply-To: to -general; this isn't a development
problem.]

We have tables with multimillion records, and they are fast.  But not
fast to count().  The MVCC design of PostgreSQL will give you very
few concurerncy problems, but you pay for that in the response time
of certain kinds of aggregates, which cannot use an index.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: [HACKERS] []performance issues

Rod Taylor <rbt@zort.ca> — 2002-08-02T18:08:02Z

On Fri, 2002-08-02 at 11:39, Andrew Sullivan wrote:
> On Fri, Aug 02, 2002 at 03:48:39PM +0400, Yaroslav Dmitriev wrote:
> > 
> > So I am still interested in PostgreSQL's ability to deal with 
> > multimillon records tables.
> 
> [x-posted and Reply-To: to -general; this isn't a development
> problem.]
> 
> We have tables with multimillion records, and they are fast.  But not
> fast to count().  The MVCC design of PostgreSQL will give you very
> few concurerncy problems, but you pay for that in the response time
> of certain kinds of aggregates, which cannot use an index.

Of course, as suggested this is easily overcome by keeping your own c
counter.

begin;
insert into bigtable values ();
update into counttable set count=count+1;
commit;

Now you get all the fun concurrency issues -- but fetching the
information will be quick.   What happens more, the counts, or the
inserts :)

Re: [HACKERS] []performance issues

Andrew Sullivan <andrew@libertyrms.info> — 2002-08-02T18:11:09Z

On Fri, Aug 02, 2002 at 09:57:16AM -0600, David Blood wrote:
> 
> Count() is slow even on your Sun server with 16gb ram?  How big is the
> database?

Well, just relatively slow!  It's always going to be relatively slow
to seqscan a few million records.  We have some tables which have
maybe 4 or 4.5 million records in them.  (I don't spend a lot of time
count()ing them ;-)

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: []performance issues

ngpg@grymmjack.com — 2002-08-02T18:27:44Z

> Hello,
> 
> Sorry if it's wrong list for the question. Could you suggest some
> tweaks to the PostgreSQL 7.2.1 to handle the following types of tables
> faster? 
> 
> Here we have  table "stats" with  something over one millon records. 
> Obvious "SELECT COUNT(*) FROM stats " takes over 40 seconds to
> execute, and this amount of time does not shorten considerably in
> subsequent similar requests. All the databases are vacuumed nightly.
> 
> CREATE TABLE "stats" (
>    "url" varchar(50),
>    "src_port" varchar(10),
>    "ip" varchar(16),
>    "dst_port" varchar(10),
>    "proto" varchar(10),
>    "size" int8,
>    "login" varchar(20),
>    "start_date" timestamptz,
>    "end_date" timestamptz,
>    "aggregated" int4
> );
> CREATE  INDEX "aggregated_stats_key" ON "stats" ("aggregated");
> CREATE  INDEX "ip_stats_key" ON "stats" ("ip");
> 
> stats=> explain select count(*) from stats;
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=113331.10..113331.10 rows=1 width=0)
>   ->  Seq Scan on stats  (cost=0.00..110085.28 rows=1298328 width=0)
> 
> EXPLAIN
> stats=> select count(*) from stats;
>   count  
> ---------
>  1298328
> (1 row)
> 
> The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512
> Mb DDR, ATA 100 HDD. 
> 
> Thanks in advance,
> Yar
> 

I have been dealing with a similar problem.. First I switched to scsi, 
second I installed enough memory and increased shared memory (in both 
freebsd kernel and pg.conf) so that the entire database could fit into 
ram;  this combined with the summary table idea keeps me out of most 
trouble

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-02T20:09:55Z

Thomas Lockhart wrote:
> ...
> > Perhaps a more relevant question is why are we cluttering the namespace
> > with any such function at all?  What's the use case for it?  We've
> > gotten along fine without one so far, and I don't really think that we
> > *ought* to be exposing random bits of internal implementation details
> > at the SQL level.
> 
> Actually, I was wondering the same thing, maybe for a different reason.
> Exposing the backend internals could have security implications (though
> don't make me concoct a scenario to prove it ;)
> 
> Although it might have some usefulness for debugging, I think it should
> not be an "installed by default" feature, so istm would be a great
> candidate for a contrib/ function or library. If someone needs it, it is
> almost immediately available.

It was requested because it is exposed in libpq and people need it to
generate unique names and stuff like that from within psql and
functions.  Seems like a valid use for the pid.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-02T20:25:06Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Perhaps a more relevant question is why are we cluttering the namespace
> > with any such function at all?  What's the use case for it?

> It was requested because it is exposed in libpq and people need it to
> generate unique names and stuff like that from within psql and
> functions.  Seems like a valid use for the pid.

The sole reason libpq exposes it is so that you can tell a self-notify
from an incoming notify.  (ie, given you are LISTENing on a condition
that both you and other clients send NOTIFYs for, is this particular
message one that you sent yourself, or not?  Compare the originator PID
in the NOTIFY message to your backend_pid to find out.)  I put that
feature in back around 6.4, because it allowed some important
optimizations in an app I had that used LISTEN/NOTIFY a lot.

Since NOTIFY messages aren't even visible at the SQL level, the above is
not a reason for making PIDs visible at the SQL level.

I'm really dubious about using backend PID for the sort of purpose you
suggest.  Unique names would be *much* more safely handled with, say,
a sequence generator.  If you are not using libpq or another client
library that can give you a backend-PID API call, then very likely you
don't have a lot of control over the backend connection either, and
shouldn't assume that backend PID is going to be stable for you.
(Think about pooled connections in a webserver, etc.)

Finally, the most legitimate uses of PID (like discovering a backend PID
to send SIGINT to, when some client query is running wild) are not
supported at all by a function that can only return your own backend's
PID, because that's seldom the PID you need to know.  The
pg_stat_activity view handles this much better.

So I'm still unconvinced that we need or want this ...

			regards, tom lane

Re: []performance issues

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-03T10:07:50Z

> So I am still interested in PostgreSQL's ability to deal with
> multimillon records tables.

Postgres has no problem with multimillion row tables - many people on this
list run them - just don't do sequential scans on them if you can't afford
the time it takes.

Chris

Re: getpid() function

Hannu Krosing <hannu@tm.ee> — 2002-08-03T12:55:36Z

On Sat, 2002-08-03 at 01:25, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Perhaps a more relevant question is why are we cluttering the namespace
> > > with any such function at all?  What's the use case for it?
> 
> > It was requested because it is exposed in libpq and people need it to
> > generate unique names and stuff like that from within psql and
> > functions.  Seems like a valid use for the pid.
> 
> The sole reason libpq exposes it is so that you can tell a self-notify
> from an incoming notify.  (ie, given you are LISTENing on a condition
> that both you and other clients send NOTIFYs for, is this particular
> message one that you sent yourself, or not?  Compare the originator PID
> in the NOTIFY message to your backend_pid to find out.)  I put that
> feature in back around 6.4, because it allowed some important
> optimizations in an app I had that used LISTEN/NOTIFY a lot.
> 
> Since NOTIFY messages aren't even visible at the SQL level, the above is
> not a reason for making PIDs visible at the SQL level.

When I last time showed how backend_pid function can be trivially
defined as 

hannu=# create function getpid() returns int
hannu-# as '/lib/libc.so.6','getpid' language 'C';
CREATE
hannu=# select getpid();
 getpid 
--------
   2832
(1 row)

You claimed that NOTIFY uses some _other_ backend id (i.e. not process
id).

But when I now tested it it seems that this is not the case, notify does
use the actual process id.

hannu=# listen a;
LISTEN
hannu=# notify a;
NOTIFY
Asynchronous NOTIFY 'a' from backend with pid 2832 received.

> 
> So I'm still unconvinced that we need or want this ...
> 

And you can do it trivially as long as we support old-style C functions
anyway.

------------
Hannu

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-03T16:30:09Z

Hannu Krosing <hannu@tm.ee> writes:
> You claimed that NOTIFY uses some _other_ backend id (i.e. not process
> id).

I did?  Must have been momentary brain fade on my part.  It's always
been process ID.

			regards, tom lane

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-04T01:11:55Z

As I remember, most cases where people have recently been asking for
backend pid were related to temp tables because they were named by pid. 
I don't think they are anymore.  (?)

We can do two things.  We can either rename it to pg_backend_pid and
move it to the statistics section in the docs, where the backend pids of
all active backends are available, or remove my code additions and see
if anyone asks for it in 7.3.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Perhaps a more relevant question is why are we cluttering the namespace
> > > with any such function at all?  What's the use case for it?
> 
> > It was requested because it is exposed in libpq and people need it to
> > generate unique names and stuff like that from within psql and
> > functions.  Seems like a valid use for the pid.
> 
> The sole reason libpq exposes it is so that you can tell a self-notify
> from an incoming notify.  (ie, given you are LISTENing on a condition
> that both you and other clients send NOTIFYs for, is this particular
> message one that you sent yourself, or not?  Compare the originator PID
> in the NOTIFY message to your backend_pid to find out.)  I put that
> feature in back around 6.4, because it allowed some important
> optimizations in an app I had that used LISTEN/NOTIFY a lot.
> 
> Since NOTIFY messages aren't even visible at the SQL level, the above is
> not a reason for making PIDs visible at the SQL level.
> 
> I'm really dubious about using backend PID for the sort of purpose you
> suggest.  Unique names would be *much* more safely handled with, say,
> a sequence generator.  If you are not using libpq or another client
> library that can give you a backend-PID API call, then very likely you
> don't have a lot of control over the backend connection either, and
> shouldn't assume that backend PID is going to be stable for you.
> (Think about pooled connections in a webserver, etc.)
> 
> Finally, the most legitimate uses of PID (like discovering a backend PID
> to send SIGINT to, when some client query is running wild) are not
> supported at all by a function that can only return your own backend's
> PID, because that's seldom the PID you need to know.  The
> pg_stat_activity view handles this much better.
> 
> So I'm still unconvinced that we need or want this ...
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-04T02:19:36Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> As I remember, most cases where people have recently been asking for
> backend pid were related to temp tables because they were named by pid. 

Ah, good point.

> I don't think they are anymore.  (?)

Check.

> We can do two things.  We can either rename it to pg_backend_pid and
> move it to the statistics section in the docs, where the backend pids of
> all active backends are available, or remove my code additions and see
> if anyone asks for it in 7.3.

Let's take it out and wait to see if anyone really still wants it.

			regards, tom lane

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-04T03:03:21Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > As I remember, most cases where people have recently been asking for
> > backend pid were related to temp tables because they were named by pid. 
> 
> Ah, good point.
> 
> > I don't think they are anymore.  (?)
> 
> Check.
> 
> > We can do two things.  We can either rename it to pg_backend_pid and
> > move it to the statistics section in the docs, where the backend pids of
> > all active backends are available, or remove my code additions and see
> > if anyone asks for it in 7.3.
> 
> Let's take it out and wait to see if anyone really still wants it.

Just when I am ready to throw it away, I come up with a use for the
function:

	test=> select * from pg_stat_activity where procpid != backend_pid();

This shows all activity _except_ my session, which pgmonitor or others
may want to use, and I can think of no other way to do it.

Comments?  Maybe this is why it should be called pg_backend_id and put
in the stat section.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-04T03:32:15Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Let's take it out and wait to see if anyone really still wants it.

> Just when I am ready to throw it away, I come up with a use for the
> function:

> 	test=> select * from pg_stat_activity where procpid != backend_pid();

> This shows all activity _except_ my session, which pgmonitor or others
> may want to use, and I can think of no other way to do it.

Hm.  Actually this seems like an argument for exposing MyBackendId, since
what pg_stat_activity really depends on is BackendId.  But as that view
is presently defined, you'd not be able to write
	WHERE backendid = my_backend_id()
because the view doesn't expose backendid.

> Comments?  Maybe this is why it should be called pg_backend_id and put
> in the stat section.

*Please* don't call it pg_backend_id --- that invites confusion with
BackendId which is a different thing.

I'd suggest pg_backend_pid.

			regards, tom lane

Re: getpid() function

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-04T03:38:09Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Let's take it out and wait to see if anyone really still wants it.
> 
> > Just when I am ready to throw it away, I come up with a use for the
> > function:
> 
> > 	test=> select * from pg_stat_activity where procpid != backend_pid();
> 
> > This shows all activity _except_ my session, which pgmonitor or others
> > may want to use, and I can think of no other way to do it.
> 
> Hm.  Actually this seems like an argument for exposing MyBackendId, since
> what pg_stat_activity really depends on is BackendId.  But as that view
> is presently defined, you'd not be able to write
> 	WHERE backendid = my_backend_id()
> because the view doesn't expose backendid.

Yes.

> > Comments?  Maybe this is why it should be called pg_backend_id and put
> > in the stat section.
> 
> *Please* don't call it pg_backend_id --- that invites confusion with
> BackendId which is a different thing.
> 
> I'd suggest pg_backend_pid.

Sorry, I mean pg_backend_pid.   I could expose backend_id but it may
confuse people so pid is probably better.  If you had the id, you could
use pg_stat_get_backend_pid() to get the pid.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: getpid() function

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-04T03:44:01Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Sorry, I mean pg_backend_pid.

Okay, I was unsure if that was a typo or not.

> I could expose backend_id but it may
> confuse people so pid is probably better.  If you had the id, you could
> use pg_stat_get_backend_pid() to get the pid.

Yeah, I thought of suggesting pg_backend_id() to return MyBackendId and
then pg_stat_get_backend_pid() to get the PID, but was stopped by the
thought that this breaks down if the stats collector isn't running.
While I'm not convinced that there's any need for backend PID that's not
connected to looking at stats-collector results, it's probably foolish
to set up a mechanism that doesn't work outside that context.  Let's go
with pg_backend_pid().

			regards, tom lane

Re: [HACKERS] []performance issues

Peter A. Daly <petedaly@ix.netcom.com> — 2002-08-05T17:22:20Z

We have tables of over 3.1 million records. Performance is fine for most 
things as long as access hits an index. As already stated, count(*) 
takes a long time. Just took over a minute for me to check the record 
count. Our DB is primarily a data warehouse role. Creating an index on a 
char(43) field on that table from scratch takes a while, but I think 
that's expected. Under normal loads we have well under 1 second "LIKE" 
queries on that the indexed char(43) field in the table with a join on a 
table of 1.1 million records using a char(12) primary key.

Server is a Dell PowerEdge 2400, Dual PIII 667's with a gig of memory, 
800 something megs allocated to postgres shared buffers.

-Pete

Andrew Sullivan wrote:

>On Fri, Aug 02, 2002 at 03:48:39PM +0400, Yaroslav Dmitriev wrote:
>  
>
>>So I am still interested in PostgreSQL's ability to deal with 
>>multimillon records tables.
>>    
>>
>
>[x-posted and Reply-To: to -general; this isn't a development
>problem.]
>
>We have tables with multimillion records, and they are fast.  But not
>fast to count().  The MVCC design of PostgreSQL will give you very
>few concurerncy problems, but you pay for that in the response time
>of certain kinds of aggregates, which cannot use an index.
>
>A
>
>  
>

Re: [HACKERS] []performance issues

Andrew Sullivan <andrew@libertyrms.info> — 2002-08-06T21:45:00Z

On Fri, Aug 02, 2002 at 02:08:02PM -0400, Rod Taylor wrote:
> 
> Of course, as suggested this is easily overcome by keeping your own c
> counter.
> 
> begin;
> insert into bigtable values ();
> update into counttable set count=count+1;
> commit;
> 
> Now you get all the fun concurrency issues -- but fetching the
> information will be quick.   What happens more, the counts, or the
> inserts :)

You could get around this with a trigger that just inserts 1 into one
table (call it counter_unposted), and then using an external process
to take those units, add them to the value in counter_posted, and
delete them from counter_unposted.  You'd always be a few minutes
behind, but you'd get a counter that's pretty close without too much
overhead.  Of course, this raises the obvious question: why use
count() at all?

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110