pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnalyze makes queries slow...
3+ messages / 2 participants
[nested] [flat]
* Analyze makes queries slow...
@ 2003-08-06 15:18 Stef <svb@ucs.co.za>
0 siblings, 1 reply; 3+ messages in thread
From: Stef @ 2003-08-06 15:18 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a problem :
A select statement that selects from 7 tables,
groups the information by 6 columns of the
tables involved.
When there are no rows in pg_statistics,
the query runs under 3 minutes.
When I analyze the biggest table of the 7
(approx 1000000 rows), the query takes longer than
12 Hours (Had to kill it eventually).
I have the explain plan of the original, under 3 minutes
query, and would like to reverse engineer this, to
build up a query with proper join statements, as I understand
that this is a way of forcing the planner to join the table
in a faster way.
I got very close a couple of times, but still can't get it 100%
the same as what the planner did prior to analyzing.
The database is actually faster when analyzed, except
for two or three multiple join queries (which don't finish
after analyze) So I would like to make an exception
for the tables that are used in these queries only,
or do proper joins.
Can anybody help, or give some links to good
help resources?
TIA
Stefan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Analyze makes queries slow...
@ 2003-08-08 16:24 Jonathan Gardner <jgardner@jonathangardner.net>
parent: Stef <svb@ucs.co.za>
0 siblings, 1 reply; 3+ messages in thread
From: Jonathan Gardner @ 2003-08-08 16:24 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 06 August 2003 08:18, Stef wrote:
>
> Can anybody help, or give some links to good
> help resources?
>
Try the performance list.
Attach the create statements used to create the tables, the query you are
running, and the different explain plans that were generated. If you know
what it is, you may also want to attach the relevant statistics from the
pg_stats table. They will be most helpful.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/M87QWgwF3QvpWNwRAgMfAJ9c6O9EyVbJYiguv/b2wtf/NsLZfACgq3lW
YjpMSEMZ4gfyGaGoJcOjpSo=
=4/hA
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Analyze makes queries slow...
@ 2003-08-08 17:45 Stef <svb@ucs.co.za>
parent: Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 0 replies; 3+ messages in thread
From: Stef @ 2003-08-08 17:45 UTC (permalink / raw)
To: Jonathan Gardner <jgardner@jonathangardner.net>; +Cc: pgsql-sql
On Fri, 8 Aug 2003 09:24:48 -0700
Jonathan Gardner <jgardner@jonathangardner.net> wrote:
=> Try the performance list.
Thanks for the tip
Stef
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-08-08 17:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-06 15:18 Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-08 16:24 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-08 17:45 ` Stef <svb@ucs.co.za>
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