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:
> 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.
