pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
tuning large selects
2+ messages / 2 participants
[nested] [flat]

* tuning large selects
@ 1999-10-29 19:36  Werner Reisberger <wreis@datacomm.ch>
  0 siblings, 1 reply; 2+ messages in thread

From: Werner Reisberger @ 1999-10-29 19:36 UTC (permalink / raw)
  To: pgsql-sql@hub.org

I am running a db (v. 6.4.2) accessible via DBI/Pg from the internet. 
Recently I observed several running postmaster processes consuming lots of CPU,
resulting in a high load of the machine. There was nobody connected to
the db from the net. Later I saw that my db partition was filled to 100 %
by a huge file (pg_psort.189xx). My db partition is approx. 950 MB. The
db consumes 15 MB and the file consumed the rest. 

I guess the pg_psort file grew to this dimension because the select
possibilities I am offering via the CGI are not well designed. Maybe
someone could give me some advice how to make it more efficient. 

The select in question accesses only one table (notcat) with approx.
6000 rows and 15 columns. The CGI allows to choose how many columns will
be returned as a result and what columns are used to constrain the search.
There are 8 columns which can be used to constrain the search. The values
of each such column are Or-ed together in the where clause. The columns
itself are AND-ed.

Example:

select * from notcat where (col1 ~* 'val1' OR col1 ~* 'val2' ....) AND
         (col2 ~* 'val1' OR col2  ~* 'val2' ....) AND (col3 ~* 'val1' ...


To make things worse there could be several dozens values in each column
used in the where clause.

--Werner



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] tuning large selects
@ 1999-10-30 04:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Werner Reisberger <wreis@datacomm.ch>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 1999-10-30 04:11 UTC (permalink / raw)
  To: Werner Reisberger <wreis@datacomm.ch>; +Cc: pgsql-sql@hub.org

Werner Reisberger <wreis@datacomm.ch> writes:
> I am running a db (v. 6.4.2) accessible via DBI/Pg from the internet.
> Recently I observed several running postmaster processes consuming
> lots of CPU, resulting in a high load of the machine. There was nobody
> connected to the db from the net. Later I saw that my db partition was
> filled to 100 % by a huge file (pg_psort.189xx). My db partition is
> approx. 950 MB. The db consumes 15 MB and the file consumed the rest.

Yipes.  I can't see any obvious reason that a sort temp file would
even be created from the query you describe, much less that it would
grow to many times the size of the original table.  What does
"explain" show for the query you were running?

BTW: the existing sort code misbehaves if the disk fills up (it fails
to notice write errors on its temp file).  I've fixed this for 7.0,
I believe, but I don't really think that's the immediate cause of
your problem.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-10-30 04:11 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-29 19:36 tuning large selects Werner Reisberger <wreis@datacomm.ch>
1999-10-30 04:11 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox