Seq scans on indexed columns. - Mailing list pgsql-performance

    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);