Q: performance on some selects (7.0.2)?
От | Emils Klotins |
---|---|
Тема | Q: performance on some selects (7.0.2)? |
Дата | |
Msg-id | 39DB1CFB.10000.5BA3CB0@localhost обсуждение исходный текст |
Ответы |
Re: Q: performance on some selects (7.0.2)?
|
Список | pgsql-sql |
I have the following tables: ****************** Table "articles" Attribute | Type | Modifier -------------+---------+----------id | integer |title | text |authorid | integer |sourceid | integer|createddate | date |createdtime | time |publishdate | date |publishtime | time |categoryid | integer|groupid | integer |lead | text |body | text |status | integer |typeid | integer|hot | integer |parentid | integer | Indices: articles_categoryid, articles_createddate, articles_createdtime, articles_groupid, articles_hot, articles_publish_datetime, articles_sourceid, articles_status, articles_typeid -- Table "articles_groups"Attribute | Type | Modifier -----------+---------+----------articleid | integer | not nullgroupid | integer | not null --- Table "newscategories" Attribute | Type | Modifier ---------------+--------------+----------------------id | integer | not nullparentid | integer | not null default '0'name | varchar(255) | not null default ''directoryname | varchar(255) | not null default ''metakeywords | text |status | integer | not null default 1sortnr | integer | not null default0level | integer | not null default 1fullpath | text |section | integer | Index: newscategories_pkey ********************* A typical query runs like this: SELECT a.id,a.title,c.fullpath,c.section FROM articles a,articles_groups x,newscategories c WHERE x.articleid=a.id AND a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER BY a.createddate desc,a.createdtime desc LIMIT 3 Explain says: NOTICE: QUERY PLAN: Sort (cost=171.93..171.93 rows=1 width=56) -> Nested Loop (cost=0.00..171.92 rows=1 width=56) -> Nested Loop (cost=0.00..169.95rows=1 width=36) -> Seq Scan on articles_groups x (cost=0.00..12.10 rows=1 width=4) -> Seq Scan on articles a (cost=0.00..135.55 rows=636 width=32) -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 width=20) EXPLAIN Now, as I understand the thing that slows everything is the Seq scan on articles. I wonder why should it be that the query can't use index? TIA! Emils
В списке pgsql-sql по дате отправления: