Seq Scan

Can you send the relevant tables, indexes, and query

--dc--
----- Original Message -----
From: "Linh Luong" <linh.luong@computalog.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, May 28, 2001 12:23 PM
Subject: [GENERAL] Seq Scan

Hi,

The same question with seq scan
I am trying to execute this query I wrote and it is not returning at a
reasonable time to the browser. I took the query and ran EXPLAIN on it
and it displayed that every JOIN I do it is doing a Seq Scan on it. I
 have indexes on that table but it doesn't seem to use it.

Is there a way I can ensure that postgres will use my indexes?

Thanks again

This is part of my SQL statement. There are 2 other UNIONS to it they
are very similar.

select LogEvent.EventID as EventID, 0 as RequestID, LogEvent.RepairID as
RepairID,
LogEvent.ServiceOrderID as ServiceOrderID, LogEvent.UnitID as UnitID,
OperationType.ID as OperationID,
LogOperationData.Service as Service, NULL::int as FlaggedToolID,
NULL::text as FlaggedToolPanelID,
NULL as FlaggedToolCodeID, NULL as FlaggedToolVersion, NULL as
FlaggedSerialNumber,
LogEvent.EventTime as RequestDate, ServiceOrder.EngineerID as LoggerID,
LogEvent.Comment as RequestComment,
Customer.ID as CustomerID, LogOperationData.MaximumTemp as Temp,
LogEvent.StackDepth as Depth,
LogEvent.LostRigTime as LRT, NULL::int as
ReviewedByTechForRepairRequest, LogEvent.DirectionUp as DirectionUp,
NULL::int as MorningDataID, NULL::int as MorningDataFailureID,
LogEvent.AssignedStationID as StationID,
ToolHistory.RepairDate as RepairDate, ToolHistory.EmployeeID as
RepairTechID, ToolHistory.ToolID as RepairedToolID,
ToolHistory.ToolActionID as RepairActionID, ToolHistory.Comment as
RepairComment, ToolHistory.TechHour as TechHour,
ToolHistory.FailureTypeID as FailureTypeID,
ToolHistory.FailureCategory1ID::text as FailureCategory1ID,
ToolHistory.FailureCategory2ID::text as FailureCategory2ID,
ToolRepair.RepairComplete as RepairComplete,
(select count(HistoryID) from ToolHistory where
RepairID=LogEvent.RepairID) as CountOfTH

from
(OperationType JOIN
((LogOperation left JOIN LogOperationData on
LogOperation.UnitID=LogOperationData.UnitID and
LogOperation.OperationID=LogOperationData.OperationID) JOIN
(LogEvent JOIN
(Employee Emp1 JOIN
(ServiceOrder JOIN Customer
ON (ServiceOrder.CustomerID=Customer.ID))
ON (ServiceOrder.EngineerID=Emp1.ID))
ON (LogEvent.RepairRequest=1))
ON (LogOperation.ServiceOrderID=ServiceOrder.ServiceOrderiD AND
LogEvent.UnitID=LogOperation.UnitID and LogEvent.EventTime <
LogOperation.StopTime AND
LogEvent.ServiceOrderID=LogOperation.ServiceOrderID AND
LogEvent.EventTime >= LogOperation.StartTime))
ON OperationType.ID=LogOperation.OperationTypeID) LEFT JOIN

(Employee Emp2 JOIN
(Tool Tool1 JOIN
(ToolRepair JOIN ToolHistory
ON (ToolHistory.RepairID = ToolRepair.RepairID))
ON (ToolHistory.ToolID=Tool1.ID))
ON (ToolHistory.EmployeeID = Emp2.ID))

ON LogEvent.RepairID=ToolHistory.RepairID

Here is my Explain result

Nested Loop (cost=906.49..4348.12 rows=2 width=220)
-> Nested Loop (cost=185.99..1436.57 rows=1 width=132)
-> Nested Loop (cost=185.99..1435.39 rows=1 width=128)
-> Nested Loop (cost=182.32..1417.61 rows=1 width=72)
-> Seq Scan on logevent (cost=0.00..201.62 rows=1
width=52)
-> Materialize (cost=1168.33..1168.33 rows=3812
width=20)
-> Hash Join (cost=182.32..1168.33 rows=3812
width=20)
-> Hash Join (cost=108.51..813.93
rows=3812 width=16)
-> Seq Scan on serviceorder
(cost=0.00..420.12 rows=3812 width=12)
-> Hash (cost=101.01..101.01
rows=3001 width=4)
-> Seq Scan on customer
(cost=0.00..101.01 rows=3001 width=4)
-> Hash (cost=67.25..67.25 rows=2625
width=4)
-> Seq Scan on employee emp1
(cost=0.00..67.25 rows=2625 width=4)
-> Materialize (cost=14.74..14.74 rows=135 width=56)
-> Hash Join (cost=3.66..14.74 rows=135 width=56)
-> Seq Scan on logoperation (cost=0.00..3.35
rows=135 width=32)
-> Hash (cost=3.33..3.33 rows=133 width=24)
-> Seq Scan on logoperationdata
(cost=0.00..3.33 rows=133 width=24)
-> Seq Scan on operationtype (cost=0.00..1.08 rows=8 width=4)
-> Materialize (cost=2812.82..2812.82 rows=7899 width=88)
-> Hash Join (cost=720.50..2812.82 rows=7899 width=88)
-> Hash Join (cost=646.69..2192.76 rows=7899 width=84)
-> Hash Join (cost=178.05..1250.52 rows=7899
width=80)
-> Seq Scan on toolhistory
(cost=0.00..461.99 rows=7899 width=72)
-> Hash (cost=158.64..158.64 rows=7764
width=8)
-> Seq Scan on toolrepair
(cost=0.00..158.64 rows=7764 width=8)
-> Hash (cost=437.11..437.11 rows=12611 width=4)
-> Seq Scan on tool tool1 (cost=0.00..437.11
rows=12611 width=4)
-> Hash (cost=67.25..67.25 rows=2625 width=4)
-> Seq Scan on employee emp2 (cost=0.00..67.25
rows=2625 width=4)
SubPlan
-> Aggregate (cost=481.74..481.74 rows=1 width=4)
-> Seq Scan on toolhistory (cost=0.00..481.74 rows=1
width=4)

I have tried set enable_seqscan to off it made it worst.

Nested Loop (cost=200002766.01..200006908.86 rows=2 width=220)
-> Nested Loop (cost=100000537.59..100002489.38 rows=1 width=132)
-> Nested Loop (cost=100000537.59..100002487.36 rows=1
width=128)
-> Nested Loop (cost=100000537.59..100002455.35 rows=1
width=72)
-> Seq Scan on logevent
(cost=100000000.00..100000201.62 rows=1 width=52)
-> Materialize (cost=2206.07..2206.07 rows=3812
width=20)
-> Hash Join (cost=537.59..2206.07 rows=3812
width=20)
-> Hash Join (cost=318.86..1706.76
rows=3812 width=16)
-> Index Scan using
serviceorder_pkey on serviceorder (cost=0.00..1102.60 rows=3812
width=12)
-> Hash (cost=311.36..311.36
rows=3001 width=4)
-> Index Scan using
customer_pkey on customer (cost=0.00..311.36 rows=3001
width=4)
-> Hash (cost=212.16..212.16 rows=2625
width=4)
-> Index Scan using employee_pkey
on employee emp1 (cost=0.00..212.16 rows=2625 width=4)
-> Materialize (cost=28.97..28.97 rows=135 width=56)
-> Merge Join (cost=0.00..28.97 rows=135 width=56)
-> Index Scan using logoperation_pkey on
logoperation (cost=0.00..12.49 rows=135 width=32)
-> Index Scan using logoperationdata_pkey on
logoperationdata (cost=0.00..12.46 rows=133 width=24)
-> Index Scan using operationtype_pkey on operationtype
(cost=0.00..2.01 rows=1 width=4)
-> Materialize (cost=100004320.74..100004320.74 rows=7899 width=88)
-> Hash Join (cost=100002228.42..100004320.74 rows=7899
width=88)
-> Hash Join (cost=100002009.69..100003555.77 rows=7899
width=84)
-> Hash Join (cost=100000524.25..100001596.72
rows=7899 width=80)
-> Seq Scan on toolhistory
(cost=100000000.00..100000461.99 rows=7899 width=72)
-> Hash (cost=504.84..504.84 rows=7764
width=8)
-> Index Scan using toolrepair_pkey on
toolrepair (cost=0.00..504.84 rows=7764 width=8) ->
Hash (cost=1453.92..1453.92 rows=12611 width=4)
-> Index Scan using tool_pkey on tool tool1
(cost=0.00..1453.92 rows=12611 width=4)
-> Hash (cost=212.16..212.16 rows=2625 width=4)
-> Index Scan using employee_pkey on employee emp2
(cost=0.00..212.16 rows=2625 width=4)
SubPlan
-> Aggregate (cost=100000481.74..100000481.74 rows=1 width=4)
-> Seq Scan on toolhistory (cost=100000000.00..100000481.74
rows=1 width=4)

Thanks

--
Linh Luong
Computalog Ltd.
Software Developer
Phone: (780) 464-6686 (ext 325)
Email: linh.luong@computalog.com

Linh

I just created two simple tables, and joined them via an integer
If there isn't a sequence on the foreign key then postgres uses a seq scan,
if there is an index then he uses an index scan.

One thing you can try is to make sure the types are compatible. ie make sure
you aren't joining an integer and a text field

Dave

----- Original Message -----
From: "Linh Luong" <linh.luong@computalog.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, May 28, 2001 2:16 PM
Subject: [GENERAL] Re: Seq Scan