Thread: postgres slower than grep?

postgres slower than grep?

From
Spiros Ioannou
Date:

Hello all,

before you start reading, have in mind that this is not post to advertise
one db over another, I just want to know what happens, that's why I did
the comparisons with other db.
to the point:

This is my table:

           Table "table1"
 Attribute |    Type     | Modifier
-----------+-------------+----------
 ClientID  | integer     |
 firstname | varchar(5)  |
 surname   | varchar(22) |
 area      | varchar(3)  |
 neigh     | varchar(27) |
 dimos     | varchar(50) |
 tel       | varchar(7)  |

The Facts:
----------
The table exported to a text file is about 330MB, and contains about 5
milion lines.

The same table takes 670MB in postgres. Why?
(I tried mysql, and it uses 340MB).

issuing the following query:
select * from table1 where tel='7485842';
takes about 1min and 40 seconds to complete. (query has one result)

explain of the above returns:
Seq Scan on table1  (cost=0.00..147835.01 rows=23 width=76)

issuing a grep in the text file takes about 25 seconds! *(for the whole grep
to finish - worse case)*

issuing the same query in mysql takes about 25 seconds.

-to test the disk speed, I doubled the size of the text file by copying
 it twice and the same grep query took 51 seconds (as expected)

when creating an index the query completes of course in no time

yes , i tried vacuum

Postgres is version 7.0
system is solaris 2.7
hard disk is 10000rpm, ultraSCSI
cpu is UltraSparcIIi 333Mhz
physical memory is 384MB

and now the questions
---------------------
-shouldn't postgres be at least as fast as the grep?
-shouldn't the table data file be smaller to reduce disk activity? Why is it
 double as the mysql same data file or the text file? I also noticed that
 an index file for column "tel" is about 130MB large and the mysql's one
 was 64MB, is this normal?



Thank you for your time,

Spiros  Ioannou
e-mail:sivann@kill-9.gr
---------------------------------------
Image Video & Multimedia Systems Lab.
Department of Electrical & Computer Eng.
National Technical University of Athens