Skip site navigation (1) Skip section navigation (2)

Optimizing a request

From: Jean-Max Reymond <jmreymond(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimizing a request
Date: 2004-08-31 18:59:11
Message-ID: 4b09a0c0408311159fa91802@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Matt ClarkDate: 2004-08-31 18:59:55
Subject: Re: Table UPDATE is too slow
Previous:From: Steinar H. GundersonDate: 2004-08-31 18:48:45
Subject: Re: Table UPDATE is too slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group