Diff between Primary key and non primary key select

On Friday 14 Feb 2003 12:30 pm, Deepa wrote:

Base database size Time taken to select one record using one
query in milliseconds

With Primary key With Non primary Key

2000 15.8 15.7
10000 71.2 70.7
25000 174 173.5
100000 4319 3417

In the above result, I cannot able to see much difference between
the time consumed using primary key and non-primary key value. Even in
some cases, I can able to see that using non primary key value retrieves
record faster than using primary key.

What indexes do you have? How big are the fields?
If the above are figures for 1 row selected, then no indexes are used. You can
see what the system is doing with:

EXPLAIN ANALYSE SELECT (rest of query here)

I did the above test several times and I am getting the same
results. Can any one tell me, what could be the possible reason for this.

I ran the above test in the following system configuration.

SYSTEM CONFIGURATION
====================

Hard Disk : 4.3 GB
RAM : 64 MB
Processor : Celeron - 566 MHz
Swap space : 200 MB

Get more RAM! There's no space for PG to work in with only 64MB.

--
Richard Huxton

Deepa <kdeepa@midascomm.com> writes:

In the above result, I cannot able to see much difference between
the time consumed using primary key and non-primary key value. Even in
some cases, I can able to see that using non primary key value retrieves record faster
than using primary key.

Try running "analyze tablename". If you don't run analyze periodically
postgres won't know about changes in the data such as a table growing large
enough to warrant using an index.

If that doesn't help, run your query in psql with the word "explain" before it
and send the results. Also send the output of \d tablename.

explain will say exactly what postgres is doing to run your query. It looks
like the query with the primary key may not be using an index.

--
greg