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