From: Jean-Max Reymond <jmreymond@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Optimizing a request
Date: Tue, 31 Aug 2004 20:59:11 +0200
Message-ID: <4b09a0c0408311159fa91802@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jmreymond@gmail.com
Subject: Re: Optimizing a request
In-Reply-To: <4b09a0c0408311159fa91802@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox