Postgres query with PHP takes too long when it should not

Question

 SELECT a.COL1, a.COL2, b.COL3 ,
 a.COL4, e.COL1 , e.COL5,  e.COL6,
 e.COL7, a.COL8,b.COL9, b.COL10 , a.COL11,
 f.COL12 FROM TABLE1 a, TABLE2 b
 TABLE3 c, TABLE4 d, TABLE5 e, TABLE6 f 
 WHERE a.COL1=b.COL1 and a.COL2=c.COL2 and c.COL3=d.COL3 and d.COL5='2'
 ORDER BY date(a.COL8) DESC, a.COL2 desc, a.COL1

The query above when executed directly on the database using PGAdmin or Navicat is returning 570 results and finishes in less than a second. However when executed via pg_query it takes about 40(!) seconds.

Does anyone know why this is?

idipous · Accepted Answer · 2013-03-08 20:03:06Z

2

Well it turned out that the answer was pretty weird. I was using a connection supplied by a function someone else wrote sometime in the past... turns out it was using

pg_query($db_platform, "set enable_seqscan=false");

No wonder that the query was broken when using that connection.

answered Mar 8, 2013 at 20:03

idipous

2,9103 gold badges38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

"'cos indexes are always better, mmmkay?". This is why I wish Pg had in-query hints, because without them people make harder-to-debug horrible hacks like this. Hints are bad, but this sort of thing is worse.

Collectives™ on Stack Overflow

Postgres query with PHP takes too long when it should not

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related