| Lists: | pgsql-novicepgsql-odbc |
|---|
| From: | HK <harikrishnan(at)midascomm(dot)com> |
|---|---|
| To: | <pgsql-novice(at)postgresql(dot)org> |
| Subject: | accessing primary key slower than accessing non primary key??? |
| Date: | 2003-02-13 13:59:25 |
| Message-ID: | Pine.LNX.4.33.0302131922060.5940-100000@mallard.midascomm.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice pgsql-odbc |
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-
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | HK <harikrishnan(at)midascomm(dot)com> |
| Cc: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Re: accessing primary key slower than accessing non primary key??? |
| Date: | 2003-02-13 15:45:37 |
| Message-ID: | 13513.1045151137@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice pgsql-odbc |
HK <harikrishnan(at)midascomm(dot)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
| From: | Ren Salomo <rene(at)ibiz(dot)com(dot)br> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Cc: | pgsql-odbc(at)postgresql(dot)org |
| Subject: | Using unixODBC/psqlODBC -- Guidelines/Tips/Manaul? |
| Date: | 2003-02-13 18:50:50 |
| Message-ID: | 20030213165050.0e92f9dc.rene@ibiz.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice pgsql-odbc |
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...