Re: Re: Seq Scan - Mailing list pgsql-general

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
>