Re: What happens between end of explain analyze and end of query execution ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Apr 4, 2013, at 2:49 PM, Franck Routier <franck.routier@xxxxxxxxx> wrote:

Le 04/04/2013 18:25, Tom Lane a écrit :
Franck Routier <franck.routier@xxxxxxxxx> writes:
Right, explain alone takes 3.6 seconds, so the time really seems to go 
query planning...
Well, you've not shown us the query, so it's all going to be
speculation.  But maybe you have some extremely expensive function that
the planner is evaluating to fold to a constant, or something like that?
The generated plan isn't terribly complicated, but we can't see what
was required to produce it.

			regards, tom lane


The request is not using any function. It looks like this:

SELECT *
         FROM sanrss
         LEFT JOIN sanrum  ON sanrum.sanrum___rforefide = sanrss.sanrss___rforefide AND sanrum.sanrum___rfovsnide = sanrss.sanrss___rfovsnide AND sanrum.sanrum___sanrsside = sanrss.sanrsside
            LEFT JOIN sanact  ON sanact.sanact___rforefide = sanrum.sanrum___rforefide AND sanact.sanact___rfovsnide = sanrum.sanrum___rfovsnide AND sanact.sanact___sanrsside = sanrum.sanrum___sanrsside AND sanact.sanact___sanrumide = sanrum.sanrumide AND sanact.sanact___sanrumide   IS NOT NULL AND sanact.sanact___rsanopide='CCAM'
            LEFT JOIN sandia  ON sandia.sandia___rforefide = sanrum.sanrum___rforefide AND sandia.sandia___rfovsnide = sanrum.sanrum___rfovsnide AND sandia.sandia___sanrsside = sanrum.sanrum___sanrsside AND sandia.sandia___sanrumide = sanrum.sanrumide AND sandia.sandiasig=1
            LEFT JOIN saneds  ON sanrss.sanrss___rforefide = saneds.saneds___rforefide AND sanrss.sanrss___rfovsnide = saneds.saneds___rfovsnide AND sanrss.sanrss___sanedside = saneds.sanedside
            LEFT JOIN rsaidp  ON saneds.saneds___rforefide = rsaidp.rsaidp___rforefide AND saneds.saneds___rsaidpide = rsaidp.rsaidpide
              WHERE sanrss.sanrss___rforefide =  'CHCL'  AND sanrss.sanrss___rfovsnide =  '201012_600'  AND sanrss.sanrsside =  '1188100'
         ORDER BY sanrum.sanrumord, sanrum.sanrumide


Schema looks like this :
rsaidp
 |
 v
sanrss --------
 |            |
 v            v
sanrum       sandia
 |
 v
sanact


Primary keys are most made of several varchars. Foreign keys do exist. Query is getting these data for one specific sanrss.
This used to take around 50ms to execute, and is now taking 3.5 seconds. And it looks like this is spent computing a query plan...

I also tried: PREPARE qry(id) as select ....
The prepare takes 3.5 seconds. Execute qry(value) takes a few milliseconds...

Regards,
Franck

Is it only this query or all queries?

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux