New User - options and other "get going" questions.

Hi,
   I have just installed the lastest PostGreSQL version as an RPM.  I am 
using the /etc/init.d/postgresql file provided to start and stop. I am not 
that conversant with shell programming (YET).

   I want to keep tables in "/rwhdata2/data" rather than 
/var/lib/pgsql/data".  I presume that $PGDATA is set to 
"/var/lib/pgsql/data", but I can't find out where.

   Typing "echo $PGDATA" logged in as either root or postgres returns a 
blank line.  Can someone point me in the right direction.  I have three 
books on my desk that purport to explain Postgresql and they all mention 
PGDATA but none clearly delineate where it is set.

   Thanks,

Bob

Please reply directly as I am not sure that I am getting any list traffic 
even though I checked to receive all messages when I subscribed.  Thanks!

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

news groups

Hi again,
   How can I log into news.postgresql.org to follow the messages in the 
news groups.  I am using Mozilla-0.9.8 and don't seem to be able to get it 
to see any of the lists.

Thanks,

Bob
--

-- 

Bob Hartung, Bettendorf, IA

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

Re: New User - options and other "get going" questions.

BobH writes:

>    I want to keep tables in "/rwhdata2/data" rather than
> /var/lib/pgsql/data".  I presume that $PGDATA is set to
> "/var/lib/pgsql/data", but I can't find out where.

It's set somewhere in the init script.  (The exact location may vary with
the version.)  It might be easier to make a symlink from the "standard"
location to your desired location.

--

-- 
Peter Eisentraut   peter_e <at> gmx.net

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

Re: news groups

Bob Hartung wrote:
> Hi again,
>    How can I log into news.postgresql.org to follow the messages in the 
> news groups.  I am using Mozilla-0.9.8 and don't seem to be able to get it 
> to see any of the lists.

I just pointed my mozilla 0.9.9 at news.postgresql.org and can see the
lists just fine.

--

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman <at> 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

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: New User - options and other "get going" questions.

Bob-

I'm pasting in a chunk from some of my local documentation for our system.
In our case, we don't touch the default, but rather set up some new
locations, but you may want to do something similar, and the description of
the process may help if you still want to change the default location. The
bits in parentheses are examples of the commands that work on our system-
you'll want to substitute your own directories and database names of course.

1)If an initdb was not done by the package install, you may have to do it:
(su; su - postgres; initdb)
2)Create the directories that you want the database(s) to live in. Make the
directory owner postgres (mkdir /data/staging; chown postgres /data/staging)
3)Define DB data locations as environment variables in
/var/lib/postgres/.profile eg: export PG_STAGING=/data/staging
4)Restart postmaster to pick up the new env variable: /etc/init.d/postgresql
restart
5)Initialize the database location: (su - postgres; initlocation PG_STAGING)
6)Create the database: (createdb staging -D PG_STAGING)

Regards,

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf <at> ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

> -----Original Message-----
> From: pgsql-admin-owner <at> postgresql.org

Will an outer join on two indexed fields use the indexes?

Hi-

I've got another "plan" question- I'm trying to tune a query that uses an
outer join on two indexed (not unique) fields. I did an explain on it & was
surprised to find that the index wasn't used:

monroe=# explain select * from
monroe-#        (charge left outer join criminal_disposition on
monroe(#          (charge.charge_id = criminal_disposition.charge_id));
NOTICE:  QUERY PLAN:

Hash Join  (cost=260.68..21110.40 rows=147101 width=360)
  ->  Seq Scan on charge  (cost=0.00..4883.01 rows=147101 width=252)
  ->  Hash  (cost=150.94..150.94 rows=5894 width=108)
        ->  Seq Scan on criminal_disposition  (cost=0.00..150.94 rows=5894
width=108)

My question is- Does the fact that this is an outer join cause this, or is
soem other factor involved? If it is the outer join, is there a different
way to join these which will be more efficient?

Thanks!

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf <at> ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

---------------------------(end of broadcast)---------------------------

Re: Will an outer join on two indexed fields use the indexes?

"Nick Fankhauser" <nickf <at> ontko.com> writes:
> monroe=# explain select * from
> monroe-#        (charge left outer join criminal_disposition on
> monroe(#          (charge.charge_id = criminal_disposition.charge_id));
> NOTICE:  QUERY PLAN:

> Hash Join  (cost=260.68..21110.40 rows=147101 width=360)
>   ->  Seq Scan on charge  (cost=0.00..4883.01 rows=147101 width=252)
>   ->  Hash  (cost=150.94..150.94 rows=5894 width=108)
>         ->  Seq Scan on criminal_disposition  (cost=0.00..150.94 rows=5894
> width=108)

This seems like a perfectly reasonable plan to me, given that query,
and assuming that the row-count estimates aren't completely out of touch
with reality.  A mergejoin-based plan isn't obviously better, and a
nestloop-based plan is almost certainly worse.  (You could try forcing
those plan types and comparing the actual runtimes if you doubt it.)
If you had additional constraints --- say, a WHERE clause that selects
just one or a few rows of "charge" --- then a different plan type might
be more appropriate.

> My question is- Does the fact that this is an outer join cause this, or is
> soem other factor involved?

A left join constrains the planner's choices somewhat (it can't choose
to put the lefthand table on the inside of the join, for example).  In
this case I doubt that's making any difference.  Anyway, if you need an
outer join then you need it --- there are no better alternatives.

			regards, tom lane

table dump

Re: table dump

On Tue, 9 Apr 2002, Jodi Kanter wrote:

> I just completed a data only dump of a table by using the following command:
>
> pg_dump genex -Rau -t species > species.sql
>
> I noticed that the first line of the file seems to be disabling some
> postgres trigger. It reads:
>
> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'species';
>
> Then all the data is listed.
>
> At the end of the file the triggers are enabled again with the
> following command:
>
> UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger
> where pg_class.oid = tgrelid) WHERE relname = 'species';
>
> Can someone explain why this is happening? The problem is that I am
> coping this dump into another schema creation file that I have. I want
> the data from this particular file included in with my new database.
> However, I am creating this database with a user account so that all
> tables will be owned by that user. I think the problem here is that
> this trigger stuff requires that you be signed in as postgres in order
> to do anything with the pg_class table. correct?

You'd need to be a superuser I'd guess.  The reason for those statmenets
is to turn off foreign key constraints during the load of what's assumed
to be correct data since it came from a dump.  You can ignore them if you
don't have any or don't mind having the constraints run.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

Timestamps and performances problems

Hello all.
  I've a performance problem on specific requests :

  When I use timestamps + interval in where clauses, query performance is
 slowed down by a factor of 20 or 30!!!! For exemple : 
  	select timestamp,value 
  	from measure 
  	where timestamp<now() and timestamp>(now() - '1 hour'::interval) 

  	is 20 to 30 times longer than 

  	select timestamp,value 
  	from measure 
  	where timestamp<'2002-04-10 10:00' and timestamp>='2002-04-10 9:00'; 

  So where is the bottleneck?
  A paradigm seems that now() and (now() - '1hour'::interval) is evaluated for
  each row comparison... Am I right? Thus is there a way to make SQL
 interpreter evaluate this by rewriting them before launching any comparisons?

  Or do I have to rewrite all my application queries and calculate each time
  now() and interval predicates?

 Thanks by advance

--

-- 
Jean-Christophe ARNU
s/w developer 
Paratronic France
MR: J'ai beaucoup entendu parler de fcol, mais je n'y suis jamais alle
MR: jeter un oeil....  c'est quoi l'adresse ?
CL: Tu viens d'y poster :)
-+- in Guide de linuxien pervers : "Termes abscons..." -+-

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org