We are working on migrating our database from oracle to postgres.
Postgres tablesize is twice than oracle tablesize for all my
tables.And so the query also takes twice as much time than oracle. So
we were checking to see what makes postgres slower than oracle even
for basic full tablescan queries.
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?
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...)
Do you have any comments on this?
Thanks in advance
josh
On Nov 20, 2007 12:37 AM, Trevor Talbot <quension@gmail.com> wrote:
> On 11/19/07, Josh Harrison <joshques@gmail.com> wrote:
>
> > I have 2 tables with 2 cols each( 1 numeric(8,0) and 1 varchar(3) ).
> > In table1 both the cols are filled and in table2 the varchar colm is null
> >
> > So when I checked the tablesize for these two tables (using pg_relation_size)
> > table1 - 57344 bytes (no null columns)
> > table2 - 49152 bytes (varchar colm is null)
> >
> > There is not much difference between the two sizes.So even if a column
> > is null postgresql still has lots of overhead.
> > Does postgres occupy space even when the column is NULL?
>
> PostgreSQL's disk storage works in "pages", where each page is 8KB.
> It will use as much space within each page as it can. Filip's last
> link details this.
>
> Is there a specific reason you're looking at this, as in you have some
> requirement to meet? Or just curious how it works?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>