pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSeq scans on indexed columns.
3+ messages / 3 participants
[nested] [flat]
* Seq scans on indexed columns.
@ 2008-01-14 14:18 Yannick Le Guédart <yannick@over-blog.com>
2008-01-14 14:34 ` Re: Seq scans on indexed columns. Guillaume Cottenceau <gc@mnc.ch>
2008-01-14 19:06 ` Re: Seq scans on indexed columns. Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 2 replies; 3+ messages in thread
From: Yannick Le Guédart @ 2008-01-14 14:18 UTC (permalink / raw)
To: 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);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Seq scans on indexed columns.
2008-01-14 14:18 Seq scans on indexed columns. Yannick Le Guédart <yannick@over-blog.com>
@ 2008-01-14 14:34 ` Guillaume Cottenceau <gc@mnc.ch>
1 sibling, 0 replies; 3+ messages in thread
From: Guillaume Cottenceau @ 2008-01-14 14:34 UTC (permalink / raw)
To: Yannick Le Guédart <yannick@over-blog.com>; +Cc: pgsql-performance
Yannick Le Guédart <yannick 'at' over-blog.com> writes:
> 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.
This is a FAQ entry (curious that the people there didn't direct
you to the FAQ first):
http://www.postgresql.org/docs/faqs.FAQ.html#item4.6
It's possible that more than 800,000 rows is not a "small
percentage of the rows in the table", and also there's the fact
that "indexes are normally not used (..) to perform joins".
Also, your database may not be analyzed, or not properly tuned
(for example, it seems that the default random_page_cost of 4 is
too large for current disks - at least for us, we've found that 2
is more correct)
[...]
> -> Seq Scan on _user (cost=0.00..205537.72 rows=806972 width=24)
--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Seq scans on indexed columns.
2008-01-14 14:18 Seq scans on indexed columns. Yannick Le Guédart <yannick@over-blog.com>
@ 2008-01-14 19:06 ` Shane Ambler <pgsql@Sheeky.Biz>
1 sibling, 0 replies; 3+ messages in thread
From: Shane Ambler @ 2008-01-14 19:06 UTC (permalink / raw)
To: Yannick Le Guédart <yannick@over-blog.com>; +Cc: pgsql-performance
Yannick Le Guédart wrote:
> 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
First off what sort of response times are you getting with the query? or
are you just after an understanding of why it plans that way?
pg version?
What sort of row counts do you have in the tables?
Some of the steps show it expecting to get over 2M rows being returned.
How many rows are returned without the limit 5? Is it close to what you
would expect?
From what I see the first seq scan (role_id=5) can't use an index as
role_id is only the third element of an index, it would need to be
matching all three on the condition to use it. But I don't expect that
to make much difference (time wise) if any.
The first nested loop has the biggest cost estimate difference and I
would expect that to be closer to what you are looking for.
My guess is that the joins are not producing the result you expect -
start without the joins (and the joined columns) and add one at a time
to see which is causing the problem. Then find a better way to join the
data.
http://www.postgresql.org/docs/8.2/interactive/explicit-joins.html
may give you some ideas...
> 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)
>
> --------------------
>
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-01-14 19:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-14 14:18 Seq scans on indexed columns. Yannick Le Guédart <yannick@over-blog.com>
2008-01-14 14:34 ` Guillaume Cottenceau <gc@mnc.ch>
2008-01-14 19:06 ` Shane Ambler <pgsql@Sheeky.Biz>
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