<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Index not used can have a lot of causes:<br></div><div class="gmail_default" style="font-family:monospace,monospace">1. the way you write the sql query,<br></div><div class="gmail_default" style="font-family:monospace,monospace">2. a problem with statistics (vacuum analyze concerned tables)<br></div><div class="gmail_default" style="font-family:monospace,monospace">3. a wrong tuning of postgres server (less common)<br>...<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Most likely in your case is 1., but you can vacuum analyze the tables first, it could be an easy fix.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Can you show the exact query you are using (along with the number of row per concerned table)?<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2016-06-14 12:53 GMT+02:00 Michal Seidl <span dir="ltr"><<a href="mailto:michal.seidl@gmail.com" target="_blank">michal.seidl@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hello,<br>
You understand my problem correctly. But despite I have sptatial index on geometry colum (points are 2D) one SQL query takes about 20ms only with operator && ST_GeomFromText('POINT..) && geom_column. This operator as I know works only with MBR (it should be same as equal for points)<br>
<br>
With other operators ST_Distance,ST_Equal, = the spatial index is not used (?) and query take about 500ms. 500 points x 500ms => 250s which is quite a lot and I do not take into account time spent on python side. That's the problem.<br>
<br>
Thanks Michal<span class=""><br>
<br>
On 06/14/2016 10:36 AM, Rémi Cura wrote:<br>
</span><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">
Hey,<br>
I fiind it difficult to understand exactly your problem.<br>
If you have a set of point A in python , of about 100 points,<br>
and a set of point B, in postgres, of a few million points,<br>
and that you want to find which point of A are already in B,<br>
you wouldn't need to import your python points into a temp postgres<br>
table and index it.<br>
<br>
If your points are simple (for instance, only 2D, each dim an int),<br>
you may skip postgis altogether.<br>
Checking 100 points against few million should be <10ms with indexes.<br>
<br>
You can check ta<br>
<br>
<br>
2016-06-13 20:46 GMT+02:00 Leknín Řepánek <<a href="mailto:godzilalalala@gmail.com" target="_blank">godzilalalala@gmail.com</a><br></span>
<mailto:<a href="mailto:godzilalalala@gmail.com" target="_blank">godzilalalala@gmail.com</a>>>:<div><div class="h5"><br>
<br>
Python Array -> geometry(POINT, [SRID])[] -> unnest, you can use unnest<br>
in CTE<br>
something like<br>
<br>
WITH cte AS (<br>
SELECT unnest(array['POINT(1 1)'::geometry ......]) pnt<br>
)<br>
SELECT * FROM t JOIN cte ON ST_....<br>
<br>
<br>
Milions of points aren`t big data, if it is slow, maybe you should check<br>
your indexes.<br>
<br>
On Mon, Jun 13, 2016 at 08:08:34AM -0700, Michal Seidl wrote:<br>
> Hello,<br>
> I have probably simple question but I am stuck. I have table with<br>
about 1E6<br>
> points in table. I have about hundreds of points in Python array.<br>
How to<br>
> pass these points into Postgis SQL query to check against table<br>
(if the same<br>
> point exist) with millions points?<br>
><br>
> I started with Python loop to check every point individually but<br>
it is slow.<br>
> I have found lots of receipts how to fight with large data but<br>
they have to<br>
> be already in database.<br>
><br>
> Which way is good for this situation?<br>
> - create temporary table and firstly insert points into DB<br>
> - try to manage Python not to close connection and send SQL query<br>
with each<br>
> point fast<br>
> - try to push all points in one query? How?<br>
> - write stored procedure that will accept array of data?<br>
> - anything else?<br>
><br>
> May be it is more general SQL questions, but i am working with<br>
points.<br>
><br>
> Thanks for advice Michal<br>
><br>
><br>
><br>
> --<br>
> View this message in context:<br>
<a href="http://postgis.17.x6.nabble.com/Performance-external-loop-WHERE-IN-values-stored-procedure-or-else-to-pass-data-to-SQL-query-tp5010138.html" rel="noreferrer" target="_blank">http://postgis.17.x6.nabble.com/Performance-external-loop-WHERE-IN-values-stored-procedure-or-else-to-pass-data-to-SQL-query-tp5010138.html</a><br>
> Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
> _______________________________________________<br>
> postgis-users mailing list<br></div></div>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><span class=""><br>
> <a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br></span>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><span class=""><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<br>
</span></blockquote><div class="HOEnZb"><div class="h5">
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>