> > Ok, could you just for the fun of it try this in front of your query?
> > explain analyze
> > ex:
> > dbmail=# explain ANALYZE select * from messageblks;
> > QUERY PLAN
> > ---------------------------------------------------------------------------------------------------------------
> > Seq Scan on messageblks (cost=0.00..304.92 rows=1792 width=913)
> > (actual time=0.015..3.249 rows=1830 loops=1)
> > Total runtime: 4.527 ms
> > (2 rows)
>
> > Btw, I ran that from the psql console ('psql dbmail postgres').
>
> dbmail=# explain ANALYZE select * from dbmail_messageblks;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------
> Seq Scan on dbmail_messageblks (cost=0.00..0.00 rows=1 width=58) (actual
> time=0.000..190.000 rows=2502 loops=1)
> Total runtime: 280.000 ms
> (2 rows)
That looks like a reasonable time. I'm assuming this is on the new HDD.
Not sure why it estimates 1 row, but ends up with 2502 tho.
The estimates usually are off by a few %, but not that much.
This could point to a bug in pgsql query optimizer, but I'm not too
familiar with the 8.0 version yet.
> > If you run that with a few queries that uses a where clause, you
> > can immedeately see wether the db uses the indexes or not.
> > And it's also good for simple benchmarking of db schema.
>
> It is an index there, however, I saw that in an Oracle database,
> it was not used, though there was an index.
Pgsql does not always use an index if it thinks it is more expensive
than to do it without. But sometimes that assumtion is wrong when the
disk/cpu/io speed is different than that of the assumed ratio.
But it will of course only use indexes when you specify a "where id='1'"
or similar restriction in the query. I guess you know this already.
> > shared_buffers = 15200 # min 16, at least max_connections*2, 8KB each
> > sort_mem = 65536 # min 64, size in KB
> > vacuum_mem = 65536 # min 1024, size in KB
>
> shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
>
> the other both seemed to have disappeared from 8.0beta.
> I'll try to tweak the settings to use max available resources.
1000 would mean that pgsql can use about 8MB ram, and that is
way too low for any real work. I'd recommend atleast 64MB, 256MB
for any production server.
And, I think 8.0 uses the shared buffers for most of it thus the
dedicated memory to sort and vacuum is no longer needed.
> > That requires shared buffers of 122MB. To get that to work in
> > linux I added this line to the postgresql start script (in rc.d)
> > echo 256000000 > /proc/sys/kernel/shmmax
>
> How do I do this on Windows?
I do not think it is needed on windows. If it is then the symptom is
that pgsql will not start with the increased value.
On linux the default limit in the kernel is usually 32MB, that is
why whe need to increase /proc/sys/kernel/shmmax to atleast 128MB.
Hope this helps.
-=Dead2=-
