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 по дате отправления: