Re: Hi,
От | Nigel J. Andrews |
---|---|
Тема | Re: Hi, |
Дата | |
Msg-id | Pine.LNX.4.21.0302131348380.2660-100000@ponder.fairway2k.co.uk обсуждение исходный текст |
Ответ на | Hi, (Deepa <kdeepa@midascomm.com>) |
Список | 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.
В списке pgsql-general по дате отправления: