Re: postgresql storage and performance questions

On Nov 20, 2007 8:10 AM, Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> wrote:
> 2007/11/20, Josh Harrison <joshques(at)gmail(dot)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?
Version 8.2.3

> >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?

explain analyze select count(*) from dummy1

QUERY PLAN
-------------------------------
Aggregate (cost=1192999.60..1192999.61 rows=1 width=0) (actual
time=109792.239..109792.239 rows=1 loops=1)
-> Seq Scan on dummy1 (cost=0.00..1119539.48 rows=29384048
30000000 width=0) (actual time=0.027..101428.016 rows=29384048
loops=1)
Total runtime: 109792.332 ms

Postgresql takes 1m 40s for this query
Oracle takes 45 sec

It is just a count(*) query. I know count(*) query is slower in
postgres becoz it doesn't use index. But in Oracle I give the query as
select /*+full(dummy1)*/ count(*) from dummy1
with the hint so that oracle uses full table scan and not the index scan.

>
> Did you do some index tuning or do you just expect the indexes ported
> from Oracle schema to work?
I created the indexes and Im not sure what kind of tuning neds to be
done for the indexes. But this above query doesnt use any indexes.

> Did you run ANALYZE after populating database?
Yes

> What are server parameters and did you tune postgres config to fit them?
I had attached my config file and the table structure

> > 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
>

Thanks again
josh