Seq scans on indexed columns.

Greetings,

I was trying to get informations on #portgresql about a query plan I
think is quite strange, and I was said to post on this list. I hope my
mail will be clear enough. I have included the query, the query plan,
and the table definitions. I just don't understand the "Seq Scan" on
fileds that are indexed.

Thanks in advance

Yannick

Here we go :

--------------------------------

QUERY

SELECT
_article.*,
(
SELECT COUNT (id)
FROM _comment
WHERE parent_id = _article.id
) AS nb_comments,
_blog.id as blog_id,
_blog.name as blog_name,
xpath_string(_blog.data,'/data/title') as blog_title,
_blog.reference as blog_ref,
_blog.main_host as blog_main_host,
_user.id as user_id,
_user.reference as user_ref,
_user.nickname as user_nickname

FROM _article

INNER JOIN _blog
ON _article.path <@ _blog.path
INNER JOIN _entity_has_element
ON _entity_has_element.element_id = _blog.id
INNER JOIN _user
ON _user.id = _entity_has_element.entity_id
AND _entity_has_element.role_id = 5

WHERE _article.id IN
(
SELECT _relation.destination_id AS id
FROM _relation
WHERE _relation.parent_id = 1008109112
)
AND _article.date_publishing < now ()

ORDER BY nb_comments DESC
OFFSET 0
LIMIT 5

--------------------------------
QUERY PLAN
Limit (cost=378253213.46..378253213.47 rows=5 width=1185)
-> Sort (cost=378253213.46..378260027.29 rows=2725530 width=1185)
Sort Key: (subplan)
-> Hash Join (cost=4907270.58..375534454.12 rows=2725530 width=1185)
Hash Cond: (_entity_has_element.element_id = _blog.id)
-> Hash Join (cost=220747.87..260246.60 rows=543801 width=32)
Hash Cond: (_entity_has_element.entity_id = _user.id)
-> Seq Scan on _entity_has_element (cost=0.00..19696.96 rows=543801 width=16)
Filter: (role_id = 5)
-> Hash (cost=205537.72..205537.72 rows=806972 width=24)
-> Seq Scan on _user (cost=0.00..205537.72 rows=806972 width=24)
-> Hash (cost=4309146.55..4309146.55 rows=2388333 width=1161)
-> Nested Loop (cost=8782.56..4309146.55 rows=2388333 width=1161)
-> Nested Loop (cost=8689.45..43352.96 rows=5012 width=1073)
-> HashAggregate (cost=8689.45..8740.05 rows=5060 width=8)
-> Bitmap Heap Scan on _relation (cost=124.98..8674.40 rows=6021 width=8)
Recheck Cond: (parent_id = 1008109112)
-> Bitmap Index Scan on idx_relation_parent_id (cost=0.00..123.47 rows=6021 width=0)
Index Cond: (parent_id = 1008109112)
-> Index Scan using _article_pkey on _article (cost=0.00..6.83 rows=1 width=1073)
Index Cond: (_article.id = _relation.destination_id)
Filter: (date_publishing < now())
-> Bitmap Heap Scan on _blog (cost=93.11..845.15 rows=477 width=114)
Recheck Cond: (_article.path <@ _blog.path)
-> Bitmap Index Scan on gist_idx_blog_path (cost=0.00..92.99 rows=477 width=0)
Index Cond: (_article.path <@ _blog.path)
SubPlan
-> Aggregate (cost=135.81..135.82 rows=1 width=8)
-> Index Scan using idx_comment_parent_id on _comment (cost=0.00..135.61 rows=79 width=8)
Index Cond: (parent_id = $0)

--------------------

Now on the table definition :

--------------------------------------------

CREATE TABLE "_article" (
"id" bigint NOT NULL DEFAULT nextval('element_id_sequence'::regclass),
"parent_id" bigint,
"path" ltree,
"data" text,
"date_creation" timestamp without time zone NOT NULL DEFAULT now(),
"date_publishing" timestamp without time zone NOT NULL DEFAULT
now(),
"date_modification" timestamp without time zone NOT NULL DEFAULT
now(),
"counters" hstore,
"reference" integer NOT NULL DEFAULT
nextval('_article_reference_seq'::regclass),
"title" character varying NOT NULL,
"text" text,
CONSTRAINT "_article_pkey" PRIMARY KEY (id)
) WITHOUT OIDS;

-- Indexes

CREATE UNIQUE INDEX _article_pkey ON _article USING btree (id);
CREATE INDEX gist_idx_article_path ON _article USING gist (path);
CREATE INDEX idx_article_date_creation ON _article USING btree
(date_creation);
CREATE INDEX idx_article_date_modification ON _article USING btree
(date_modification);
CREATE INDEX idx_article_date_publishing ON _article USING btree
(date_publishing);
CREATE INDEX idx_article_parent_id ON _article USING btree (parent_id);
CREATE INDEX idx_article_reference ON _article USING btree (reference);

--------------------------------------------

CREATE TABLE "_blog" (
"id" bigint NOT NULL DEFAULT
nextval('element_id_sequence'::regclass),
"parent_id" bigint,
"path" ltree,
"data" text,
"date_creation" timestamp without time zone NOT NULL DEFAULT now(),
"date_publishing" timestamp without time zone NOT NULL DEFAULT
now(),
"date_modification" timestamp without time zone NOT NULL DEFAULT
now(),
"counters" hstore,
"reference" integer NOT NULL DEFAULT
nextval('_blog_reference_seq'::regclass),
"name" character varying NOT NULL,
"main_host" character varying NOT NULL,
"base_host" character varying NOT NULL,
"description" text,
"rating" integer DEFAULT 0,
CONSTRAINT "_blog_pkey" PRIMARY KEY (id)
) WITHOUT OIDS;

-- Indexes

CREATE UNIQUE INDEX _blog_pkey ON _blog USING btree (id);
CREATE INDEX gist_idx_blog_path ON _blog USING gist (path);
CREATE INDEX idx_blog_base_host ON _blog USING btree (base_host);
CREATE INDEX idx_blog_date_creation ON _blog USING btree
(date_creation);
CREATE INDEX idx_blog_date_modification ON _blog USING btree
(date_modification);
CREATE INDEX idx_blog_date_publishing ON _blog USING btree
(date_publishing);
CREATE INDEX idx_blog_main_host ON _blog USING btree (main_host);
CREATE INDEX idx_blog_name ON _blog USING btree (name);
CREATE INDEX idx_blog_parent_id ON _blog USING btree (parent_id);
CREATE INDEX idx_blog_rating ON _blog USING btree (rating);
CREATE INDEX idx_blog_reference ON _blog USING btree (reference);

--------------------------------------------

CREATE TABLE "_comment" (
"id" bigint NOT NULL DEFAULT
nextval('element_id_sequence'::regclass),
"parent_id" bigint,
"path" ltree,
"data" text,
"date_creation" timestamp without time zone NOT NULL DEFAULT now(),
"date_publishing" timestamp without time zone NOT NULL DEFAULT
now(),
"date_modification" timestamp without time zone NOT NULL DEFAULT
now(),
"counters" hstore,
"reference" integer NOT NULL DEFAULT
nextval('_comment_reference_seq'::regclass),
"text" text,
CONSTRAINT "_comment_pkey" PRIMARY KEY (id)
) WITHOUT OIDS;

-- Indexes

CREATE UNIQUE INDEX _comment_pkey ON _comment USING btree (id);
CREATE INDEX gist_idx_comment_path ON _comment USING gist (path);
CREATE INDEX idx_comment_date_creation ON _comment USING btree
(date_creation);
CREATE INDEX idx_comment_date_modification ON _comment USING btree
(date_modification);
CREATE INDEX idx_comment_date_publishing ON _comment USING btree
(date_publishing);
CREATE INDEX idx_comment_parent_id ON _comment USING btree (parent_id);
CREATE INDEX idx_comment_reference ON _comment USING btree (reference);

--------------------------------------------

CREATE TABLE "_relation" (
"id" bigint NOT NULL DEFAULT
nextval('element_id_sequence'::regclass),
"parent_id" bigint,
"path" ltree,
"data" text,
"date_creation" timestamp without time zone NOT NULL DEFAULT now(),
"date_publishing" timestamp without time zone NOT NULL DEFAULT
now(),
"date_modification" timestamp without time zone NOT NULL DEFAULT
now(),
"counters" hstore,
"destination_id" bigint NOT NULL,
CONSTRAINT "_relation_pkey" PRIMARY KEY (id)
) WITHOUT OIDS;

-- Indexes

CREATE UNIQUE INDEX _relation_pkey ON _relation USING btree (id);
CREATE INDEX gist_idx_relation_path ON _relation USING gist (path);
CREATE INDEX idx_relation_date_creation ON _relation USING btree
(date_creation);
CREATE INDEX idx_relation_date_modification ON _relation USING btree
(date_modification);
CREATE INDEX idx_relation_date_publishing ON _relation USING btree
(date_publishing);
CREATE INDEX idx_relation_destination_id ON _relation USING btree
(destination_id);
CREATE INDEX idx_relation_parent_id ON _relation USING btree
(parent_id);

--------------------------------------------

CREATE TABLE "_entity_has_element" (
"element_id" bigint NOT NULL,
"entity_id" bigint NOT NULL,
"role_id" bigint NOT NULL,
CONSTRAINT "_entity_has_element_pkey" PRIMARY KEY (element_id,
entity_id, role_id)
) WITHOUT OIDS;

-- Indexes

CREATE UNIQUE INDEX _entity_has_element_pkey ON _entity_has_element
USING btree (element_id, entity_id, role_id);
CREATE INDEX idx_element_id ON _entity_has_element USING btree
(element_id);
CREATE INDEX idx_entity_id ON _entity_has_element USING btree
(entity_id);

--------------------------------------------

CREATE TABLE "_user" (
"id" bigint NOT NULL DEFAULT
nextval('entity_id_sequence'::regclass),
"is_group" boolean,
"data" text,
"site_id" bigint,
"date_inscription" date NOT NULL DEFAULT now(),
"reference" integer NOT NULL DEFAULT
nextval('_user_reference_seq'::regclass),
"login" character varying,
"passwd" character varying NOT NULL,
"nickname" character varying,
CONSTRAINT "_user_pkey" PRIMARY KEY (id)
) WITHOUT OIDS;

-- Indexes

CREATE UNIQUE INDEX _user_pkey ON _user USING btree (id);
CREATE INDEX idx_user_login ON _user USING btree ("login");
CREATE INDEX idx_user_nickname ON _user USING btree (nickname);
CREATE INDEX idx_user_reference ON _user USING btree (reference);