Indexing Question

On Thu, Jan 12, 2006 at 01:16:00PM -0800, Mike wrote:

Hi,

My table structure is the following:

tbl_A ----one-to-many---> tbl_B ----one-to-many---> tbl_C

Since it was important for me to trace back tbl_C records back to
tbl_A, I placed a tbl_A_id inside tbl_C.

Now, in order to optimize my database for speed, I want to index my
tbl_B for it's tbl_A_id. So far so good. Now, with tbl_C, it makes
sense that all records of tbl_A sit next to eachother so I could index
tbl_A_id (which are not used as often in my queries), or index by
tbl_B_id. Or both of them.

Your breaking a tenant of good database design: normalize 'til it hurts,
denormalize 'til it works (where works in this case means performs
adequately).

In other words, don't keep tbl_a_id in tbl_c unless you know for certain
you need it for performance reasons.

To be clear, my question is: Does it make sense for me to index a table
by field_1 with the intention of having postgreSQL place those records
next to each other for faster queries that wouldn't necessarily
reference field_1?

Indexes have absolutely nothing to do with the order in which rows are
stored in a table, unless you cluster the table on an index (which is
still only temporary).

Without knowing what your normal access patterns on tbl_c will be it's
impossible to say if clustering on an index on tbl_a_id would help or
not.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I had to kill a vacuum in the middle with -9. I shut down and restarted the
postgres server several times after that but I am unable to connect to the
db that I was initially running vacuum on
I'm doing "psql dbname" and it hangs for a while. I'm still waiting. Any
ideas?
Thanks

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +0000, Sally Sally wrote:

I had to kill a vacuum in the middle with -9. I shut down and restarted the
postgres server several times after that but I am unable to connect to the
db that I was initially running vacuum on
I'm doing "psql dbname" and it hangs for a while. I'm still waiting. Any
ideas?

What's the logfile say about it?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I'm sorry that was not intentional.

Please do group replies so the list gets cc'd.

From: Sally Sally [mailto:dedeb17@hotmail.com]
The main reason I decided to kill the vacuum was because
there were several
postmaster processes spewed from scripts trying to access the
database and
they were all hanging. After killing the vacuum I decided to
restart the
server. However because of the hanging processes it was not
letting me shut
down so I had to kill the postmaster processes. The log gave
a warning about
shared memory corruption at this point. After this I started
the postmaster
did a proper shut down and restarted again. Now when I try to
connect to the
db it just hangs and there are no logs.

The last log was
LOG: database system was shut down at 2006-01-13 18:04:05 EST
LOG: checkpoint record is at 505/C80F1010
LOG: redo record is at 505/C80F1010; undo record is at 0/0;
shutdown TRUE
LOG: next transaction ID: 1341794294; next OID: 1358710904
LOG: database system is ready

Well, that looks like a clean startup. I'm not sure if anyone else on the list has any ideas. Maybe getting a stack trace of psql trying to connect would shed some light...

Does a backend get spawned when you try running psql? (Look at ps aux|grep postgres before and after running psql).

From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Sally Sally <dedeb17@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to a dabase
Date: Fri, 13 Jan 2006 16:55:16 -0600

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +0000, Sally Sally wrote:

I had to kill a vacuum in the middle with -9. I shut down

and restarted

the

postgres server several times after that but I am unable

to connect to

the

db that I was initially running vacuum on
I'm doing "psql dbname" and it hangs for a while. I'm

still waiting. Any

ideas?

What's the logfile say about it?

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

LOG: database system was shut down at 2006-01-13 18:04:05 EST
LOG: checkpoint record is at 505/C80F1010
LOG: redo record is at 505/C80F1010; undo record is at 0/0;
shutdown TRUE
LOG: next transaction ID: 1341794294; next OID: 1358710904
LOG: database system is ready

Well, that looks like a clean startup. I'm not sure if anyone else on the list has any ideas. Maybe getting a stack trace of psql trying to connect would shed some light...

Does a backend get spawned when you try running psql? (Look at ps aux|grep postgres before and after running psql).

If you telnet to localhost 5432 is it listening or does it hang? If it
hangs I would guess a firewall but I would think you would pick up on
the unix socket.

J

From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Sally Sally <dedeb17@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to a dabase
Date: Fri, 13 Jan 2006 16:55:16 -0600

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +0000, Sally Sally wrote:

I had to kill a vacuum in the middle with -9. I shut down

and restarted

the

postgres server several times after that but I am unable

to connect to

the

db that I was initially running vacuum on
I'm doing "psql dbname" and it hangs for a while. I'm

still waiting. Any

ideas?

What's the logfile say about it?

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Yes a backend gets spewed. Actually it did connect me finally (I am not sure
exactly how long it took). I checked back after about four hours and I was
connected. I have also noticed in the past that after running vacuum the
client (psql) takes longer than usual to connect.
Thanks all
sally

On Sat, Jan 14, 2006 at 04:28:39AM +0000, Sally Sally wrote:

Yes a backend gets spewed. Actually it did connect me finally (I am not
sure exactly how long it took). I checked back after about four hours and I
was connected. I have also noticed in the past that after running vacuum
the client (psql) takes longer than usual to connect.
Thanks all
sally

Well, that certainly sounds like a bug... can you come up with a test
case that others could reproduce? Can you reproduce it consistently?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461