Postgres Pro
Downloads
Home   >   mailing lists

Thread: accessing primary key slower than accessing non primary key???

accessing primary key slower than accessing non primary key???

From
HK
Date:
hi,
I am using postgresql 7.1.3 under RH linux 7.2
For sake of comparison of speeds i am doing a small experiment.
I am having a table with 20 columns (1 col with bigint as primary key)
the size of a record is 300 bytes.
On retrieving records with select query with where clause accessing a
primary key and a non primary key the times are as follows


          time in ms per query retrieval
        py key   non py key
        ------   -----------
2000            15.8     15.7
10000           71.2     70.7
25000           174      173.5
100000          4319     3417

As u see the non primary key retrieval is faster. AFAIK accessing primary
key should be faster. Can anybody throw light on why is this anamoly.

I use libpq with blocking execution of queries.
I ran this test with there being no other load in the system.

TIA.

--------
regards,
hari
      __
     / /    __  _  _  _  _ __  __         -o)
    / /__  / / / \\// //_// \\ \\/ /         /\\\\  Making things happen
   /____/ /_/ /_/\\/ /___/  /_/\\_\\        _\\_v-

Re: accessing primary key slower than accessing non primary key???

From
Tom Lane
Date:
HK <harikrishnan@midascomm.com> writes:
>           time in ms per query retrieval
>         py key   non py key
>         ------   -----------
> 2000            15.8     15.7
> 10000           71.2     70.7
> 25000           174      173.5
> 100000          4319     3417

> As u see the non primary key retrieval is faster. AFAIK accessing primary
> key should be faster. Can anybody throw light on why is this anamoly.

There's nothing magic about primary keys in Postgres; I'd expect the
results to come out the same.

The first three of these look like they're indeed the same, to within
measurement error.  Not sure about the last one; perhaps you had some
effect of physical order in the table agreeing with the ordering of the
non-primary key?

            regards, tom lane

Using unixODBC/psqlODBC -- Guidelines/Tips/Manaul?

From
Renê Salomão
Date:
Hi list,

 Is there a howto to help compiling the source of unixODBC and psqlODBC,
instead of using a rpm? I'm very found on using the source code and
compile it rather than the rpms... However in this situation I've beeing
clueless...
 I've downloaded qt_x11-free-3.1.1 (GUI), unixODBC-2.2.4 and
psqlODBC-7.2.5... I'm currently using Pg 7.2.3, but I think about moving
all data do Pg 7.3.2, so tips on both versions would be appreciate!! =o)

Thanks...