Re: use of index

On Mon, 2006-07-31 at 12:55 +1000, Chris wrote:
> Rafal Pietrak wrote:
> > strop=# EXPLAIN ANALYZE SELECT * from users where username =
> > current_user;
> > QUERY
> > PLAN
> > ---------------------------------------------------------------------------------------------------------------------------
> > Index Scan using users_username_key on users (cost=0.00..5.83 rows=1
> > width=80) (actual time=0.061..0.061 rows=0 loops=1)
> > Index Cond: (username = ("current_user"())::text)
> > Total runtime: 0.193 ms
> > (3 rows)
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > EXPLAIN ANALYZE SELECT * from ludzie where username = current_user;
> > QUERY
> > PLAN
> > -------------------------------------------------------------------------------------------------
> > Seq Scan on ludzie (cost=0.00..1.19 rows=1 width=80) (actual
> > time=0.096..0.096 rows=0 loops=1)
> > Filter: (username = ("current_user"())::text)
> > Total runtime: 0.185 ms
> > (3 rows)
> >
> Firstly always start a new thread instead of replying to an existing
> one, it is a lot easier to follow and not so easily missed.

Very sorry for that, 20-yrs old habits are really deap in your bones. I
thought I've adjusted the subject accordingly, but I'll try to remember
that for my future posts anyway.

> After your insert you need to do an 'analyze users' to get postgresql
> statistics up to date.

OK. That did it. they are identical, now.

> Next - why does it need to use an index? In these examples your query is

To explain. With any 'programming exercise' I do, I 'start small' and
try to see program behavior on small scale (both datasets and number of
involved modules) before I roll out any larger setup for testing.

In this case, tha DB will be used with 'TABLE ludzie' popolated with
close to a milion entries, so when I noticed 'Seq-scan' I became
warried.

But this is a DBMS, which I can see now, and I should have trusted it a
little more. After your suggestion I've generated some more rows, and
the results are OK:

+++++++++++++++++++++++++++++++++++++++++++
test_db# \i file-with-10-rows.sql
test_db# EXPLAIN ANALYZE SELECT * from users where username =
current_user;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using username_id_key on ludzie (cost=0.00..5.99 rows=1
width=80) (actual time=0.194..0.194 rows=0 loops=1)
Index Cond: (username = ("current_user"())::text)
Total runtime: 0.344 ms
(3 rows)
++++++++++++++++++++++++++++++++++++++++++
test_db# \i file-with-10000-rows.sql
test_db# EXPLAIN ANALYZE SELECT * from users where username =
current_user;
QUERY
PLAN
------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..1.25 rows=1 width=60) (actual
time=0.177..0.177 rows=0 loops=1)
Filter: (username = ("current_user"())::text)
Total runtime: 0.288 ms
(3 rows)
+++++++++++++++++++++++++++++++++++++++++

So I don't have to warry about the omission of index use in this case.

My real warry was the discrepancy of "TABLE users' v/s 'TABLE ludzie'
results - this smelled like uncontrolable, unpredictible result. But
obviosly, not being too proficient with DBMS, I didn't realise the query
plan is build from trancient estimates of access cost. I've never before
fell into the necesity to ANALYSE table, only relaying on self-estimates
the DBMS gathers along the use of the system. Obviously that's totally
wrong for pre-production system evaluation where datasets are cooked and
swapped faster then any DB self-estimates have any chance to get
collected.

BTW: I'd really love to: "CREATE TABLE users (id oid references
pg_catalog.pg_authid(iod), info text);" - why is that not possible? Or
by a similar token, I long for the ability of a database (meaning DB
dataset/aplication, as opposed to DBMS), to be able to extend the "SET
<parameter> = <value>", with application speciffic parameters/values, so
that a DB application could have it's private parameters (like user
phone/ address) in one place, and avoid cludges like the "TABLE users".

Thenx for the help.

--
-R