Ashish Karalkar wrote:
Richard Huxton <dev@xxxxxxxxxxxx> wrote: Ashish Karalkar wrote:
query which was taking seconds on the join of these two table
suddenly started taking 20/25 min
Show the EXPLAIN ANALYSE of your problem query and someone will be able
to tell you why.
Here is the output from explain analyse:
Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It
doesn't show what actually happened, just what the planner thought was
going to happen.
Are the row-estimates roughly accurate?
table structures are more or less same with delivery being parent and sms_new being child having index on deliveryid in both tables.
HashAggregate (cost=6153350.21..6153352.38 rows=174 width=32)
-> Hash Join (cost=218058.30..6153259.97 rows=6016 width=32)
Hash Cond: ("outer".deliveryid = "inner".deliveryid)
-> Seq Scan on sms_new (cost=0.00..5240444.80 rows=138939341 width=8)
Filter: ((otid)::text !~~ 'ERROR%'::text)
-> Hash (cost=218057.87..218057.87 rows=174 width=32)
Well, it knows that it's going to be expensive (cost=5240444.80). Since
it thinks you'll only get 174 rows from the other side and 6016
matching, I can't see how an index could be calculated as more expensive.
Try issuing ENABLE seq_scan=off and re-running the EXPLAIN, let's see
what cost that comes up with.
Oh, and I take it sms_new is recently vacuumed and analysed?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
