select using index in group by slower as normal fields?

From: Einar Karttunen <ekarttun(at)cs(dot)Helsinki(dot)FI>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: select using index in group by slower as normal fields?
Date: 2001-01-31 12:26:42
Message-ID: Pine.LNX.4.30.0101311417520.13682-100000@melkinpaasi.cs.Helsinki.FI
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Herb Pabst 2001-01-31 13:35:18 starting PGsql on boot...
Previous Message Justin Clift 2001-01-31 05:30:37 Re: Sizing of LARGE databases.