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.
We're going to need more information as:
Hard disk: *shrug* 20GB may be but it's not partitioned that way
Memory: 96MB
Processor: Celeron 333MHz
Swap: probably
OS: FreeBSD 3.3
PostgreSQL: 7.2.3
Tested via: psql
aca=> select count(1) from chat_post;
count
---------
1285937
(1 row)
aca=> explain analyze select * from chat_post where session_id = 2700::smallint and post_number = 265::smallint;
NOTICE: QUERY PLAN:
Index Scan using chat_post_pkey on chat_post (cost=0.00..5.27 rows=1 width=46) (actual time=53.72..53.72 rows=0 loops=1)
Total runtime: 54.10 msec
aca=> \d chat_post
Table "chat_post"
Column | Type | Modifiers
-------------+--------------------------+-----------
session_id | smallint | not null
poster_id | smallint | not null
time | timestamp with time zone | not null
post_number | smallint | not null
fts | txtidx |
Primary key: chat_post_pkey
aca=> \d chat_post_pkey
Index "chat_post_pkey"
Column | Type
-------------+----------
session_id | smallint
post_number | smallint
unique btree (primary key)
Note, the cast to the correct data type in the where. The session ids are more
or less uniformly distributed between 1 and 2750 are increase as data is added.
I didn't hang around and time the select that counted the rows it takes that
long.
--
Nigel Andrews
On Thu, 13 Feb 2003, Deepa wrote:
Show quoted text
Hi,
While testing select query with where condition for primary key
and non primary key value, I got the following resultsBase database size Time taken to select one record using one
query in millisecondsWith Primary key With Non primary Key
2000 15.8 15.7
10000 71.2 70.7
25000 174 173.5
100000 4319 3417In 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 MBSOFTWARE 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.