At this moment, in my 8.x configuration using tablespaces for logs_t and hosts_t I get 1,221,000 rows back. My new response time is 563 seconds, which is about half of what it was under 7.x with no tablepaces.

Anything else I can be looking at or tuning?

-Thanks!


> How many rows are you expecting to be returned?
>
> At 10:27 AM 4/4/2005, eanxgeek@comcast.net wrote:
> >First let me start by saying I am pretty new to Postgresql. To date I
> >have only worked with small databases, I now have a database that isn't
> >big but is big enough to create performance issues. The database is
> >roughly 450 MB. I have two tables of interest; logs and hosts. At one
> >point I had the following sql:
> >
> > SELECT * FROM hosts, logs where (logs.host_id = hosts.host_id) ORDER BY
> > date DESC, time DESC;
> >
> >This took over 1000 seconds to execute.
> >
> >I then upgraded to PGSQL 8.0.1 and created tablespace logs_t and hosts_t,
> >these are on separate controllers and separate disks.
> > List of tablespaces
> >Name | Owner | Location
> >------------+----------+-----------------
> > hosts_t | postgres | /pgdata/hosts_t
> > logs_t | postgres | /pgdata/logs_t
> >
> >I then altered the tables logs and hosts to use the new tablespace
> >respectively and I changed the sql to read:
> >
> >SELECT hostname,facility,priority,level,tag,date,time,pro gram,msg,seq FROM
> >hosts, logs where (logs.host_id = hosts.host_id) ORDER BY date DESC, time DESC;
> >
> >Under 7.x my PGDATA was on /pgdata; however, for 8.x I have left it in in
> >its default location of /var/lib/pgsql. So what is happening now is:
> >1. I am still seeing all 4x700 MHz CPUs go to 95%+ IOWAIT, appears that
> >/var is the hardest hit.
> >2. SELECT hostname,facility,priority,level,tag,date,time,pro gram,msg,seq
> >FROM hosts, logs where (logs.host_id = hosts.host_id); works but takes
> >awhile; however, adding the ORDER BY condition causes the SQL to execute
> >to the point that /var becomes full and the SQL exits with:
> >ERROR: could not write block 81940 of temporary file: No space left on device
> >HINT: Perhaps out of disk space?
> >
> >What parameters can I start "tweaking" and what can I do to addres the
> >issue of /var filling up? Below are some of my kernel and database settings:
> >
> >effective_cache_size | 1000
> >maintenance_work_mem | 16384
> >max_connections | 32
> >shared_buffers | 64
> >------ Shared Memory Segments --------
> >key shmid owner perms bytes nattch status
> >0x0052e2c1 38043648 postgres 600 1540096 2
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
>
>
>
> The Hightower Group, Inc.
> Custom Software Solutions Designed To Fit Your Business Like A Glove.
> 165 West Airport Road, Suite B/Lititz, PA 17543
> V:717-560-4002, 877-560-4002 x: 114
> F:717-560-2825
> www.hightowergroup.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match