Re: Seq Scan - Mailing list pgsql-general
From | Linh Luong |
---|---|
Subject | Re: Seq Scan |
Date | |
Msg-id | 3B1295F5.45FF0B34@computalog.com Whole thread Raw |
In response to | Seq Scan (Linh Luong <linh.luong@computalog.com>) |
List | pgsql-general |
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
pgsql-general by date: