postgres slower than grep?
От | Spiros Ioannou |
---|---|
Тема | postgres slower than grep? |
Дата | |
Msg-id | 3B462DEE.99A2E0C3@image.ece.ntua.gr обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: