Diff between Primary key and non primary key select

Diff between Primary key and non primary key select

Post by Deep » Sat, 15 Feb 2003 21:16:12



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
        ======================

Note : To avoid load in the PC, I ran only the above test and hence there
are no other loads in the PC.

--

Bye,
Deepa. K

--
Engineer,
Network Management System,
Midas Communication Technologies private Ltd,
Chennai.

---------------------------(end of broadcast)---------------------------

 
 
 

Diff between Primary key and non primary key select

Post by Richard Huxt » Sat, 15 Feb 2003 23:55:38



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

What indexes do you have? How big are the fields?
If the above are figures for 1 row selected, then no indexes are used. You =
can=20
see what the system is doing with:

EXPLAIN ANALYSE SELECT (rest of query here)

Quote:>         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
>         =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

>         Hard Disk               :       4.3 GB
>         RAM                     :       64  MB
>         Processor               :       Celeron - 566 MHz
>         Swap space              :       200 MB

Get more RAM! There's no space for PG to work in with only 64MB.

--=20
  Richard Huxton

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Diff between Primary key and non primary key select

Post by Greg Sta » Sun, 16 Feb 2003 00:02:32



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

Try running "analyze tablename". If you don't run analyze periodically
postgres won't know about changes in the data such as a table growing large
enough to warrant using an index.

If that doesn't help, run your query in psql with the word "explain" before it
and send the results. Also send the output of \d tablename.

explain will say exactly what postgres is doing to run your query. It looks
like the query with the primary key may not be using an index.

--
greg

---------------------------(end of broadcast)---------------------------

 
 
 

1. Composite Primary key or 1 primary key column with all values



If you take the second route you will soon regret that, and it will
create a messy application, as you will soon need to 'parse'
one_column *everywhere*. So : speed is not a consideration here,
maintainability is. Choose for the second route and prepare to travel
to hell.
And storing dates as strings is definitely a *BAD* idea.
A few tables with the same primary key also looks like you have severe
problems in your datamodel.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

2. VFP6 Show all Form "Code Snippets"?

3. Numeric Primary Key vs. Combination Primary Key

4. Query refresh problems with PDox 7

5. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

6. update query

7. using big primary keys v/s small primary keys

8. Primary Key / Working Code to get primary key of a table using ADO and schemas

9. Composite Primary key or 1 primary key column with all values

10. update primary key [aid] to identity(300,1) Primary key

11. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)