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