Re: Hi, - Mailing list pgsql-general

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.