pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL query _slow_
7+ messages / 3 participants
[nested] [flat]
* SQL query _slow_
@ 1999-02-21 23:02 Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-21 23:52 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Gregg Berkholtz @ 1999-02-21 23:02 UTC (permalink / raw)
To: pgsql-sql
I have a few SQL query's that are taking in the range of 30-60 seconds
to complete. They are searching tables that are no larger than 120 rows
with I believe 7 columns of data each.
Any ideas?
The SQL statement is below:
SELECT tblClientInfo.fName, tblClientInfo.lName, tblClientInfo.phoneNum,
tblClientLocation.locationName, tblTroubTick.TroubTickLastUpdate,
tblTroubTick.hoursInvested, tblSupportPersInfo.fName,
tblSupportPersInfo.lName, tblPriority.priority, tblTroubTick.assetID,
tblTroubTickStatus.troubTickStatus, tblTroubTickProblem.troubTickProblem
FROM tblClientInfo, tblTroubTick, tblClientLocation, tblSupportPersInfo,
tblPriority, tblTroubTickStatus, tblTroubTickProblem
WHERE tblTroubTick.troubTickStatusID <> 7 AND
tblClientLocation.locationID = tblClientInfo.locationID AND
tblTroubTick.clientID = tblClientInfo.clientID AND
tblTroubTick.supportPersID = tblSupportPersInfo.supportPersID AND
tblTroubTick.priorityID = tblPriority.priorityID AND
tblTroubTick.troubTickStatusID = tblTroubTickStatus.troubTickStatusID
AND tblTroubTick.troubTickProblemID =
tblTroubTickProblem.troubTickProblemID;
Thanks for any help!
Gregg Berkholtz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SQL query _slow_
1999-02-21 23:02 SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
@ 1999-02-21 23:52 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 00:01 ` Re: [SQL] SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 1999-02-21 23:52 UTC (permalink / raw)
To: gregg@gbcomputers.com; +Cc: pgsql-sql
> I have a few SQL query's that are taking in the range of 30-60 seconds
> to complete. They are searching tables that are no larger than 120 rows
> with I believe 7 columns of data each.
> Any ideas?
> The SQL statement is below:
>
> SELECT tblClientInfo.fName, tblClientInfo.lName, tblClientInfo.phoneNum,
> tblClientLocation.locationName, tblTroubTick.TroubTickLastUpdate,
> tblTroubTick.hoursInvested, tblSupportPersInfo.fName,
> tblSupportPersInfo.lName, tblPriority.priority, tblTroubTick.assetID,
> tblTroubTickStatus.troubTickStatus, tblTroubTickProblem.troubTickProblem
>
> FROM tblClientInfo, tblTroubTick, tblClientLocation, tblSupportPersInfo,
> tblPriority, tblTroubTickStatus, tblTroubTickProblem
> WHERE tblTroubTick.troubTickStatusID <> 7 AND
> tblClientLocation.locationID = tblClientInfo.locationID AND
> tblTroubTick.clientID = tblClientInfo.clientID AND
> tblTroubTick.supportPersID = tblSupportPersInfo.supportPersID AND
> tblTroubTick.priorityID = tblPriority.priorityID AND
> tblTroubTick.troubTickStatusID = tblTroubTickStatus.troubTickStatusID
> AND tblTroubTick.troubTickProblemID =
> tblTroubTickProblem.troubTickProblemID;
6.5 will have a majorly improved optimizer. We hope to start beta in a
month. Hope you can wait.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SQL query _slow_
1999-02-21 23:02 SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-21 23:52 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-02-22 00:01 ` Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-22 01:07 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Gregg Berkholtz @ 1999-02-22 00:01 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: pgsql-sql
So, if I understand correctly. I am not doing anything wrong in terms of the
SQL coding, it is just the speed of the postmaster right now.
I am new to PostgreSQL and SQL for that matter, but another question I have
would be:
Would indexes help at all? <-- If so, what should I index to get the best
speed?
Thanks
Gregg Berkholtz
Bruce Momjian wrote:
> > I have a few SQL query's that are taking in the range of 30-60 seconds
> > to complete. They are searching tables that are no larger than 120 rows
> > with I believe 7 columns of data each.
> > Any ideas?
> > The SQL statement is below:
> >
> > SELECT tblClientInfo.fName, tblClientInfo.lName, tblClientInfo.phoneNum,
> > tblClientLocation.locationName, tblTroubTick.TroubTickLastUpdate,
> > tblTroubTick.hoursInvested, tblSupportPersInfo.fName,
> > tblSupportPersInfo.lName, tblPriority.priority, tblTroubTick.assetID,
> > tblTroubTickStatus.troubTickStatus, tblTroubTickProblem.troubTickProblem
> >
> > FROM tblClientInfo, tblTroubTick, tblClientLocation, tblSupportPersInfo,
> > tblPriority, tblTroubTickStatus, tblTroubTickProblem
> > WHERE tblTroubTick.troubTickStatusID <> 7 AND
> > tblClientLocation.locationID = tblClientInfo.locationID AND
> > tblTroubTick.clientID = tblClientInfo.clientID AND
> > tblTroubTick.supportPersID = tblSupportPersInfo.supportPersID AND
> > tblTroubTick.priorityID = tblPriority.priorityID AND
> > tblTroubTick.troubTickStatusID = tblTroubTickStatus.troubTickStatusID
> > AND tblTroubTick.troubTickProblemID =
> > tblTroubTickProblem.troubTickProblemID;
>
> 6.5 will have a majorly improved optimizer. We hope to start beta in a
> month. Hope you can wait.
>
> --
> Bruce Momjian | http://www.op.net/~candle
> maillist@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SQL query _slow_
1999-02-21 23:02 SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-21 23:52 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 00:01 ` Re: [SQL] SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
@ 1999-02-22 01:07 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 06:29 ` Re: [SQL] SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 1999-02-22 01:07 UTC (permalink / raw)
To: gregg@gbcomputers.com; +Cc: pgsql-sql
> So, if I understand correctly. I am not doing anything wrong in terms of the
> SQL coding, it is just the speed of the postmaster right now.
> I am new to PostgreSQL and SQL for that matter, but another question I have
> would be:
> Would indexes help at all? <-- If so, what should I index to get the best
> speed?
Not sure. If EXPLAIN is slow showing results, it is just the optimizer.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SQL query _slow_
1999-02-21 23:02 SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-21 23:52 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 00:01 ` Re: [SQL] SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-22 01:07 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-02-22 06:29 ` Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-22 06:45 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Gregg Berkholtz @ 1999-02-22 06:29 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: pgsql-sql
EXPLAIN -- What is that?
Bruce Momjian wrote:
> > So, if I understand correctly. I am not doing anything wrong in terms of the
> > SQL coding, it is just the speed of the postmaster right now.
> > I am new to PostgreSQL and SQL for that matter, but another question I have
> > would be:
> > Would indexes help at all? <-- If so, what should I index to get the best
> > speed?
>
> Not sure. If EXPLAIN is slow showing results, it is just the optimizer.
>
> --
> Bruce Momjian | http://www.op.net/~candle
> maillist@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
--
-----------------------------------------------------
///////////// Gregg Berkholtz - Owner
| G B | Computer consulting, sales and support
| Computers |
\\\\\\\\\\\\\ INFO: www.gbcomputers.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SQL query _slow_
1999-02-21 23:02 SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-21 23:52 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 00:01 ` Re: [SQL] SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-22 01:07 ` Re: [SQL] SQL query _slow_ Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 06:29 ` Re: [SQL] SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
@ 1999-02-22 06:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-02-22 06:45 UTC (permalink / raw)
To: gregg@gbcomputers.com; +Cc: pgsql-sql
> EXPLAIN -- What is that?
A psql command.
>
> Bruce Momjian wrote:
>
> > > So, if I understand correctly. I am not doing anything wrong in terms of the
> > > SQL coding, it is just the speed of the postmaster right now.
> > > I am new to PostgreSQL and SQL for that matter, but another question I have
> > > would be:
> > > Would indexes help at all? <-- If so, what should I index to get the best
> > > speed?
> >
> > Not sure. If EXPLAIN is slow showing results, it is just the optimizer.
> >
> > --
> > Bruce Momjian | http://www.op.net/~candle
> > maillist@candle.pha.pa.us | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> --
> -----------------------------------------------------
> ///////////// Gregg Berkholtz - Owner
> | G B | Computer consulting, sales and support
> | Computers |
> \\\\\\\\\\\\\ INFO: www.gbcomputers.com
>
>
>
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SQL query _slow_
@ 1999-02-22 15:42 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 1999-02-22 15:42 UTC (permalink / raw)
To: pgsql-sql@hub.org
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Gregg Berkholtz <gregg@gbcomputers.com> writes:
>> Would indexes help at all? <-- If so, what should I index to get the best
>> speed?
> Not sure. If EXPLAIN is slow showing results, it is just the optimizer.
Assuming Bruce has correctly diagnosed the problem (I think he has),
adding indexes would make things *worse* in 6.4.*, because it'd increase
the number of cases the optimizer has to consider.
Bruce fixed several bugs in the optimizer that were causing it to
consider far more combinations than it should in queries that join a
lot of tables. So your query should work OK in 6.5, and at that point
you should try adding indexes to improve the speed of the actual query
(as opposed to the planning stage).
As a stopgap measure while you're using 6.4.*, try setting the GEQO
threshold to less than 7 tables.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-02-22 15:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-21 23:02 SQL query _slow_ Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-21 23:52 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 00:01 ` Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-22 01:07 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 06:29 ` Gregg Berkholtz <gregg@gbcomputers.com>
1999-02-22 06:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-02-22 15:42 Re: [SQL] SQL query _slow_ 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