Обсуждение: Diff between Primary key and non primary key select

Hi,
        While testing select query with where condition for primary key
and non primary key value, I got the following results

        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.

        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

        SOFTWARE CONFIGURATION
        ======================

Note : To avoid load in the PC, I ran only the above test and hence there
are no other loads in the PC.

--

Bye,
Deepa. K

--
Engineer,
Network Management System,
Midas Communication Technologies private Ltd,
Chennai.

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