Thread: Indexing Question
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. 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? Thanks, Mike
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. >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 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq