X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 932065E46CD for ; Tue, 31 Aug 2004 15:59:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19754-10 for ; Tue, 31 Aug 2004 18:59:27 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.206]) by svr1.postgresql.org (Postfix) with ESMTP id 964C85E46C2 for ; Tue, 31 Aug 2004 15:59:26 -0300 (ADT) Received: by mproxy.gmail.com with SMTP id 75so165272rnl for ; Tue, 31 Aug 2004 11:59:12 -0700 (PDT) Received: by 10.38.6.75 with SMTP id 75mr1607445rnf; Tue, 31 Aug 2004 11:59:11 -0700 (PDT) Received: by 10.38.86.41 with HTTP; Tue, 31 Aug 2004 11:59:11 -0700 (PDT) Message-ID: <4b09a0c0408311159fa91802@mail.gmail.com> Date: Tue, 31 Aug 2004 20:59:11 +0200 From: Jean-Max Reymond Reply-To: Jean-Max Reymond To: pgsql-performance@postgresql.org Subject: Optimizing a request Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200408/474 X-Sequence-Number: 8110 hi, I have the following structure in my base 7.4.2 CREATE TABLE "public"."article" ( "art_id" INTEGER NOT NULL, "rub_id" INTEGER DEFAULT '0' NOT NULL, "art_titre" VARCHAR(100) DEFAULT '' NOT NULL, "art_texte" TEXT NOT NULL, "art_date" DATE NOT NULL, "aut_id" INTEGER, CONSTRAINT "article_pkey" PRIMARY KEY("art_id") ) WITH OIDS; CREATE INDEX "article_art_date_index" ON "public"."article" USING btree ("art_date"); CREATE INDEX "article_aut_id_index" ON "public"."article" USING btree ("aut_id"); CREATE INDEX "article_rub_id_index" ON "public"."article" USING btree ("rub_id"); CREATE INDEX "article_titre" ON "public"."article" USING btree ("art_id", "art_titre"); CREATE TABLE "public"."auteur" ( "aut_id" INTEGER NOT NULL, "aut_name" VARCHAR(100) DEFAULT '' NOT NULL, CONSTRAINT "auteur_pkey" PRIMARY KEY("aut_id") ) WITH OIDS; CREATE TABLE "public"."rubrique" ( "rub_id" INTEGER NOT NULL, "rub_titre" VARCHAR(100) DEFAULT '' NOT NULL, "rub_parent" INTEGER DEFAULT '0' NOT NULL, "rub_date" DATE, CONSTRAINT "rubrique_pkey" PRIMARY KEY("rub_id") ) WITH OIDS; CREATE INDEX "rub_rub" ON "public"."rubrique" USING btree ("rub_parent"); CREATE INDEX "rubrique_rub_date_index" ON "public"."rubrique" USING btree ("rub_date"); CREATE INDEX "rubrique_rub_titre_index" ON "public"."rubrique" USING btree ("rub_titre"); I want to optimize the following request and avoid the seq scan on the table article (10000000 rows). 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) -> Seq Scan on article (cost=0.00..215629.00 rows=10000000 width=108) -> Hash (cost=8.26..8.26 rows=3 width=22) -> Index Scan using rubrique_parent on rubrique (cost=0.00..8.26 rows=3 width=22) Index Cond: (rub_parent = 8) thanks for your answers, -- Jean-Max Reymond CKR Solutions http://www.ckr-solutions.com