Jinqiang Han wrote:
> hello, all.
>
> I have a table about 2 million rows. when I run "select * from table1" in
> psql, it will take me about 10 minutes to get the result. I wonder if
> postgresql can immediately return result like db2.
If you're executing in psql, it's probably trying to load the whole result
set into RAM at once, and then try to format it, and then pipe it through a
pager (i.e. more). That's not really a test of PostgreSQL, I would
recommend using a cursor or something so that psql doesn't give you a false
impression.
>
> After that I create a index on a column named id. The time executing
> "selct * from table1 where id=10" in psql is much faster than that of
> executing "select * from table1 where id <10" in psql. why?
>
Well, that depends. First, turn on stats collecting and run "VACUUM
ANALYZE". That will collect some data about your data which helps the
planner make a good choice.
My initial guess is that the second query is not using the index. That may
happen because if "id" is an INT column, than negatives are allowed, and
the planner figures that "id<10" only narrows it down to about half the
possible rows (in which case it would choose sequential scan). However, we
humans know that the "id" column is really a positive INT (or at least I
assume it is, based on your implication that "id<10" should be about as
fast as "id=10"), therefore "id<10" really means a max 10 rows out of 2M
(which means it should do an index scan). Turning on stats and running
VACUUM ANALYZE will give the planner the information that it needs to make
an intelligent choice.
If that doesn't help you, give us the table schema and run:
EXPLAIN ANALYZE SELECT * FROM table1 WHERE id=10
and then run:
EXPLAIN ANALYZE SELECT * FROM table1 WHERE id<10
and give us the output.
Then run "VACUUM ANALYZE" every once in a while (depending on how fast your
data changes), like every night for instance.
Regards, Jeff Davis