Re: [PERFORM] Optimizing a request

2004-08-31 Thread Hervé Piedvache
Hi, Le Mardi 31 Août 2004 20:59, Jean-Max Reymond a écrit : > explain SELECT art_id, art_titre, art_texte, rub_titre > FROM article inner join rubrique on article.rub_id = rubrique.rub_id > where rub_parent = 8; > > Hash Join (cost=8.27..265637.59 rows=25 width=130) > Hash Cond: ("outer".rub_id

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Gary Doades
On 31 Aug 2004 at 22:24, Jean-Max Reymond wrote: > On Tue, 31 Aug 2004 21:16:46 +0100, Gary Doades <[EMAIL PROTECTED]> wrote: > > > I can only presume you mean 1 GB RAM. What exactly are your > > settings for shared buffers and effective_cache_size? > > for 1 GB RAM, > shared_buffers = 65536 > e

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Jean-Max Reymond
On Tue, 31 Aug 2004 16:13:58 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > That seems like a very strange plan choice given those estimated row > counts. I'd have expected it to use a nestloop with inner index scan > on article_rub_id_index. You haven't done anything odd like disable > nestloop,

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Jean-Max Reymond
On Tue, 31 Aug 2004 21:16:46 +0100, Gary Doades <[EMAIL PROTECTED]> wrote: > I can only presume you mean 1 GB RAM. What exactly are your > settings for shared buffers and effective_cache_size? for 1 GB RAM, shared_buffers = 65536 effective_cache_size = 16384 > > Can you increase default_statis

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Gary Doades
On 31 Aug 2004 at 21:42, Jean-Max Reymond wrote: > - Original Message - > From: Gary Doades <[EMAIL PROTECTED]> > Date: Tue, 31 Aug 2004 20:21:49 +0100 > Subject: Re: [PERFORM] Optimizing a request > To: [EMAIL PROTECTED] > > > > > Have you run ANA

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Tom Lane
Jean-Max Reymond <[EMAIL PROTECTED]> writes: > explain SELECT art_id, art_titre, art_texte, rub_titre > FROM article inner join rubrique on article.rub_id = rubrique.rub_id > where rub_parent = 8; > Hash Join (cost=8.27..265637.59 rows=25 width=130) > Hash Cond: ("outer".rub_id = "inner".rub_id

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Jean-Max Reymond
- Original Message - From: Gary Doades <[EMAIL PROTECTED]> Date: Tue, 31 Aug 2004 20:21:49 +0100 Subject: Re: [PERFORM] Optimizing a request To: [EMAIL PROTECTED] > Have you run ANALYZE on this database after creating the indexes or loading the > data? the indexes are

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Gary Doades
On 31 Aug 2004 at 20:59, Jean-Max Reymond wrote: > hi, > > I want to optimize the following request and avoid the seq scan on the > table article (1000 rows). > > explain SELECT art_id, art_titre, art_texte, rub_titre > FROM article inner join rubrique on article.rub_id = rubrique.rub_id

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Jean-Max Reymond
On Tue, 31 Aug 2004 12:15:40 -0700, Josh Berkus <[EMAIL PROTECTED]> wrote: > Those look suspiciously like stock estimates. When was the last time you ran > ANALYZE? the vacuum analyze ran just before the explain -- Jean-Max Reymond CKR Solutions http://www.ckr-solutions.com --

Re: [PERFORM] Optimizing a request

2004-08-31 Thread Josh Berkus
Jean, > I have the following structure in my base 7.4.2 Upgrade to 7.4.5. The version you're using has several known issues with data restore in the event of system failure. > Hash Join (cost=8.27..265637.59 rows=25 width=130) > Hash Cond: ("outer".rub_id = "inner".rub_id) > -> Seq Scan