Обсуждение: Hi,

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
        ======================

        Operation System        :       Red-Hat Linux 7.2

        Databases               :       PostgreSQL 7.1.3
        (Available with
         Red-Hat 7.2 installation)

        Programing Language     :       C language

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

--

regards,
Deepa. K

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

On Wednesday 12 Feb 2003 6:20 pm, you wrote:
> 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

That is not a correct measure. Postgresql has a latency. You select 1 record
it will return you in 200ms and 100 records in 201 ms. Does it make sense?

Try to benchmark for repeated and more number of rows as a part of query.
>
>                 With Primary key      With Non primary Key
>
>          2000                    15.8                    15.7
>          10000                   71.2                    70.7
>          25000                   174                     173.5
>              100000                  4319                    3417

Do an explain analyze on those select query. Most probably postgresql is
scanning table sequentially. Which may be a good thing.

Further you have not  specified what kind of tuning you have done to
postgresql. If you are running with defaults, well, you need to change them.

And don't read too much in a select query performance. There are lot many
things that affect the performance..

HTH

 Shridhar