pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
very slow simple query - outer join makes it quicker
3+ messages / 2 participants
[nested] [flat]

* very slow simple query - outer join makes it quicker
@ 2004-04-19 10:59  Gerard Isdell <gerard.isdell@kinetic.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Gerard Isdell @ 2004-04-19 10:59 UTC (permalink / raw)
  To: pgsql-performance

Hi Everyone 

I am new to this group and relatively new to Postgres, having used MSSQL 7 
up until now.

One table in my database in returning even the simplest of queries extremely 
slowly. The table is user table, and even the select userid from users takes 
over 20 seconds to run. There are about 2000 records in the table. 


The EXPLAIN ANALYZE on this table produces this output:
Seq Scan on users  (cost=0.00..89482.63 rows=1463 width=4) (actual 
time=68.836..40233.463 rows=1465 loops=1)
Total runtime: 40234.965 ms


SELECT USERID FROM USERS  produces this:
1465 rows fetched (25.28 sec)

The userid field is the primary key and has an index on it with this ddl: 
ALTER TABLE "public"."users" ADD CONSTRAINT "users_pkey" PRIMARY KEY 
("userid");

There are other tables, such as the messages table, that have 10s of 
thousands of rows and they return records much more quickly.


There must be something seriously wrong for simple queries like this to take 
so long.   

I should say that we are using the OpenFTS text search on the users  table.

In many cases to make the queries run at reasonable speeds I do an outer 
join on another table, and surprisingly these results come back very quickly

Can anybody help me in diagnosing this problem.


Gerard Isdell


*************************************************************************
This e-mail and any attachments may contain confidential or privileged
information.  If you are not the intended recipient, please contact the
sender immediately and do not use, store or disclose their contents.
Any views expressed are those of the individual sender and not of Kinetic 
Information System Services Limited unless otherwise stated.

                           www.kinetic.co.uk




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

* Re: very slow simple query - outer join makes it quicker
@ 2004-04-19 12:01  Rod Taylor <pg@rbt.ca>
  parent: Gerard Isdell <gerard.isdell@kinetic.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Rod Taylor @ 2004-04-19 12:01 UTC (permalink / raw)
  To: gerard.isdell@kinetic.co.uk; +Cc: pgsql-performance

> There are other tables, such as the messages table, that have 10s of 
> thousands of rows and they return records much more quickly.

> There must be something seriously wrong for simple queries like this to take 
> so long.   

Have you run VACUUM recently?

If not, run VACUUM FULL against the users table and see if that makes a
difference.




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

* Re: very slow simple query - outer join makes it quicker
@ 2004-04-19 12:36  Rod Taylor <pg@rbt.ca>
  parent: Rod Taylor <pg@rbt.ca>
  0 siblings, 0 replies; 3+ messages in thread

From: Rod Taylor @ 2004-04-19 12:36 UTC (permalink / raw)
  To: gerard.isdell@kinetic.co.uk; +Cc: pgsql-performance

On Mon, 2004-04-19 at 08:26, Gerard Isdell wrote:
> Thank, that has worked.
> 
> I've been running VACUUM regularly and thought  that would have done it. 
> 
> Obviously the FULL makes a big difference

It shouldn't. That FULL makes a significant difference says that you're
not running regular VACUUM frequently enough and/or your fsm_* settings
are too low.

> -----Original Message-----
> From: Rod Taylor <pg@rbt.ca>
> To: gerard.isdell@kinetic.co.uk
> Cc: Postgresql Performance <pgsql-performance@postgresql.org>
> Date: Mon, 19 Apr 2004 08:01:15 -0400
> Subject: Re: [PERFORM] very slow simple query - outer join makes it quicker
> 
> > > There are other tables, such as the messages table, that have 10s of 
> > > thousands of rows and they return records much more quickly.
> > 
> > > There must be something seriously wrong for simple queries like this
> > to take 
> > > so long.   
> > 
> > Have you run VACUUM recently?
> > 
> > If not, run VACUUM FULL against the users table and see if that makes a
> > difference.
> > 
> 
> 
> *************************************************************************
> This e-mail and any attachments may contain confidential or privileged
> information.  If you are not the intended recipient, please contact the
> sender immediately and do not use, store or disclose their contents.
> Any views expressed are those of the individual sender and not of Kinetic 
> Information System Services Limited unless otherwise stated.
> 
>                            www.kinetic.co.uk




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


end of thread, other threads:[~2004-04-19 12:36 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-19 10:59 very slow simple query - outer join makes it quicker Gerard Isdell <gerard.isdell@kinetic.co.uk>
2004-04-19 12:01 ` Rod Taylor <pg@rbt.ca>
2004-04-19 12:36   ` Rod Taylor <pg@rbt.ca>

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