'Re: [postgis-users] How to estimate the time taken for a query plan without actually running it?'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] How to estimate the time taken for a query plan without actually running it?
From:       Stephen Woodbridge <stephenwoodbridge37 () gmail ! com>
Date:       2020-08-03 16:32:31
Message-ID: a3c7ed53-842a-56a6-d9ee-341e1c69747a () gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

No, because the run time is dependent on what functions you apply to 
each row. For example, if you checking if a point is in a polygon, then 
the runtime is dependent on how many points are in each polygon. Really 
complex polygons, like state or county boundaries can be very expensive 
to evaluate, and may need to be reevaluted for each point. There are 
strategies to speed up queries like this, like: tiling the polygon by 
chopping it into a grid so there are many smaller and simpler polygons 
the the index works much better and and the polygons are simpler so 
faster to evaluate.

you need to explain what your data looks like to people have some 
concrete idea to make suggestions to speed it up.

-Steve W

On 8/3/2020 10:56 AM, Shaozhong SHI wrote:
> Hi,
>
> I tried to use "explain" to look into the query plan.   It only gives 
> me the cost and rows, and does not give an estimate of time to run the 
> query.
>
> "Explain analyze" gives an estimate of time needed, but for a Big Data 
> query, one has to wait to get an answer,
>
> Is there a way to get an estimate of time for running a query, without 
> actually running it.
>
> Looking forward to hearing from you.
>
> Regards,
>
> Shao
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

[Attachment #5 (text/html)]

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>No, because the run time is dependent on what functions you apply
      to each row. For example, if you checking if a point is in a
      polygon, then the runtime is dependent on how many points are in
      each polygon. Really complex polygons, like state or county
      boundaries can be very expensive to evaluate, and may need to be
      reevaluted for each point. There are strategies to speed up
      queries like this, like: tiling the polygon by chopping it into a
      grid so there are many smaller and simpler polygons the the index
      works much better and and the polygons are simpler so faster to
      evaluate.</p>
    <p>you need to explain what your data looks like to people have some
      concrete idea to make suggestions to speed it up.</p>
    <p>-Steve W<br>
    </p>
    <div class="moz-cite-prefix">On 8/3/2020 10:56 AM, Shaozhong SHI
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CA+i5JwZW0+3ESH_O9=5M1Bg8aZD06uqkAQ+Peg-nSqYP8WxLLQ@mail.gmail.com">
      <div dir="ltr">Hi,
        <div><br>
        </div>
        <div>I tried to use "explain" to look into the query plan.   It
          only gives me the cost and rows, and does not give an estimate
          of time to run the query.</div>
        <div><br>
        </div>
        <div>"Explain analyze" gives an estimate of time needed, but for
          a Big Data query, one has to wait to get an answer,</div>
        <div><br>
        </div>
        <div>Is there a way to get an estimate of time for running a
          query, without actually running it.</div>
        <div><br>
        </div>
        <div>Looking forward to hearing from you.</div>
        <div><br>
        </div>
        <div>Regards,</div>
        <div><br>
        </div>
        <div>Shao</div>
      </div>
      <br>
      <fieldset class="mimeAttachmentHeader"></fieldset>
      <pre class="moz-quote-pre" \
wrap="">_______________________________________________ postgis-users mailing list
<a class="moz-txt-link-abbreviated" \
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> <a \
class="moz-txt-link-freetext" \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
  </blockquote>
  </body>
</html>

[Attachment #6 (text/plain)]

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 