pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL 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