Re: Diff between Primary key and non primary key select - Mailing list pgsql-general

From Richard Huxton
Subject Re: Diff between Primary key and non primary key select
Date
Msg-id 200302141455.29744.dev@archonet.com
Whole thread Raw
In response to Diff between Primary key and non primary key select  (Deepa <kdeepa@midascomm.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "Robert Treat"
Date:
Subject: Re: mailing list archives
Next
From: Greg Stark
Date:
Subject: Re: Diff between Primary key and non primary key select