Thread: Seq Scan
Hi, 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 Linh
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, > > 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 > > > Linh > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
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 > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >