2007/11/20, Josh Harrison <joshques@gmail.com>:
> We are working on migrating our database from oracle to postgres.
> Postgres tablesize is twice than oracle tablesize for all my
> tables.
Interesting. Which postgresql version?
>And so the query also takes twice as much time than oracle.
This is even more interesting :) What query? can you show it here
along with EXPLAIN ANALYZE?
Did you do some index tuning or do you just expect the indexes ported
from Oracle schema to work?
Did you run ANALYZE after populating database?
What are server parameters and did you tune postgres config to fit them?
> So
> we were checking to see what makes postgres slower than oracle even
> for basic full tablescan queries.
I'm curious too :) please let me know if you resolve this mystery :)
> There were a couple of things we noted.
> 1. Tablesize twice as much than oracle-- Im not sure if postgres null
> columns has any overhead since we have lots of null columns in our
> tables.Does postgresql has lots of overhead for null columns?
I've expained this previously - you have a bitmap in each tuple.
Bitmap size is (NATTS+7) % 8
> 2. Oracle seems to be reading larger bocks than postgresql (when we
> examined the iostat and vmstat) (we had set postgres' db block size as
> 8 and oracle's is 16kb...)
yes, 8 kB is default pg block size. it is not recommended to change it
- however it could be useful in some situations - but I doubt it would
speedup your queries twice, whatever they are.
--
Filip Rembiałkowski