select using index in group by slower as normal fields?

Поиск
Список
Период
Сортировка
От Einar Karttunen
Тема select using index in group by slower as normal fields?
Дата
Msg-id Pine.LNX.4.30.0101311417520.13682-100000@melkinpaasi.cs.Helsinki.FI
обсуждение исходный текст
Ответ на cannot connect to database  (Laurent GALAIS <lg@4js.com>)
Ответы Re: select using index in group by slower as normal fields?
Список pgsql-novice
I have a table called student from which I want to return all students
to an application. If I do a query like SELECT * FROM STUDENT; explain
tells me that the cost is between 0 and 13.44. The result is same if the
table is ordered by fname, lname or class. If I order by id, then
postgresql uses index scan which takes from 0 to 43.09. Are the values
given by explain just bad or why does it appear that sorting a table
is in this case, better with any other than the index key? I have used
VACUUM and VACUUM ANALYZE periodically.

- Einar Karttunen


ekarttun=# \d student
                             Table "student"
 Attribute |   Type   |                     Modifier
-----------+----------+--------------------------------------------------
 id        | integer  | not null default nextval('student_id_seq'::text)
 fname     | char(15) |
 lname     | char(15) |
 class     | char(3)  |
Index: student_pkey
Constraint: (class ~ '^[0-9][0-9][A-Z]$'::text)

ekarttun=# explain select * from student;
NOTICE:  QUERY PLAN:

Seq Scan on student  (cost=0.00..13.44 rows=644 width=40)

EXPLAIN
ekarttun=# explain select * from student order by fname;
NOTICE:  QUERY PLAN:

Sort  (cost=43.49..43.49 rows=644 width=40)
  ->  Seq Scan on student  (cost=0.00..13.44 rows=644 width=40)

EXPLAIN
ekarttun=# explain select * from student order by lname;
NOTICE:  QUERY PLAN:

Sort  (cost=43.49..43.49 rows=644 width=40)
  ->  Seq Scan on student  (cost=0.00..13.44 rows=644 width=40)

EXPLAIN
ekarttun=# explain select * from student order by class;
NOTICE:  QUERY PLAN:

Sort  (cost=43.49..43.49 rows=644 width=40)
  ->  Seq Scan on student  (cost=0.00..13.44 rows=644 width=40)

EXPLAIN
ekarttun=# explain select * from student order by id;
NOTICE:  QUERY PLAN:

Index Scan using student_pkey on student  (cost=0.00..43.09 rows=644
width=40)

EXPLAIN





В списке pgsql-novice по дате отправления:

Предыдущее
От: Justin Clift
Дата:
Сообщение: Re: Sizing of LARGE databases.
Следующее
От: Herb Pabst
Дата:
Сообщение: starting PGsql on boot...