<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
The precise numbers are as follows:<br>
<br>
Total Rows in the table: 45922415 <br>
<br>
if I use:<br>
<tt><small>select <br>
pos_lat,<br>
pos_lon<br>
from<br>
feed_all.common_pos_messages<br>
where<br>
st_contains(ST_GeomFromText('POLYGON((0.00433541700872238
51.6875086022247,1.4450615615687
51.6875086022247,1.4450615615687
51.2018083846302,0.00433541700872238
51.2018083846302,0.00433541700872238 51.6875086022247))', 4326),
pos_point)</small></tt><br>
<br>
I get back: 4493678 in 4.77 mins<br>
<br>
Doing explain analyze I get:<br>
<tt><small>"Bitmap Heap Scan on common_pos_messages
(cost=82262.99..522647.01 rows=771600 width=8) (actual
time=127735.424..198141.843 rows=4493678 loops=1)"<br>
" Recheck Cond:
('0103000020E61000000100000005000000D29145A403C2713F0B23294800D84940E04E3ADFF81EF73F0B23294800D84940E04E3ADFF81EF73FCC056EDBD4994940D29145A403C2713FCC056EDBD4994940D29145A403C2713F0B23294800D84940'::geometry
&& pos_point)"<br>
" Filter:
_st_contains('0103000020E61000000100000005000000D29145A403C2713F0B23294800D84940E04E3ADFF81EF73F0B23294800D84940E04E3ADFF81EF73FCC056EDBD4994940D29145A403C2713FCC056EDBD4994940D29145A403C2713F0B23294800D84940'::geometry,
pos_point)"<br>
" -> Bitmap Index Scan on idx_pos (cost=0.00..82070.09
rows=2314801 width=0) (actual time=127732.000..127732.000
rows=4493679 loops=1)"<br>
" Index Cond:
('0103000020E61000000100000005000000D29145A403C2713F0B23294800D84940E04E3ADFF81EF73F0B23294800D84940E04E3ADFF81EF73FCC056EDBD4994940D29145A403C2713FCC056EDBD4994940D29145A403C2713F0B23294800D84940'::geometry
&& pos_point)"<br>
"Total runtime: 199206.428 ms"</small></tt><br>
<br>
Obviously the times and the rows do not seem to much with the actual
results. However after creating my own index based on lat/lon values
(integers) on the same table, executing this:<br>
<tt><small>select <br>
pos_lat,<br>
pos_lon<br>
from<br>
feed_all.common_pos_messages<br>
where <br>
pos_lat between 30721085 and 31012505 and<br>
pos_lon between 2601 and 867037</small></tt><br>
<br>
I get back 4493680 in 2.8 mins<br>
<br>
Doing explain analyze I get:<br>
<tt><small>"Bitmap Heap Scan on common_pos_messages
(cost=161748.26..601144.64 rows=1686719 width=8) (actual
time=10064.427..60738.808 rows=4493680 loops=1)"<br>
" Recheck Cond: ((pos_lat >= 30721085) AND (pos_lat <=
31012505) AND (pos_lon >= 2601) AND (pos_lon <= 867037))"<br>
" -> Bitmap Index Scan on idx_lat_lon
(cost=0.00..161326.58 rows=1686719 width=0) (actual
time=10061.108..10061.108 rows=4493680 loops=1)"<br>
" Index Cond: ((pos_lat >= 30721085) AND (pos_lat
<= 31012505) AND (pos_lon >= 2601) AND (pos_lon <=
867037))"<br>
"Total runtime: 61850.720 ms"</small></tt><br>
<br>
The predictions are still "out" from the actual but the btree index
seems to behave better.<br>
<br>
Any suggestions? Probably I need to bring up to date my statistics.
<br>
<br>
Kind Regards<br>
Yiannis<br>
<br>
<br>
On 30/03/2011 13:30, Sandro Santilli wrote:
<blockquote cite="mid:20110330123049.GF22262@keybit.net" type="cite">
<pre wrap="">On Wed, Mar 30, 2011 at 10:58:57AM +0100, Ioannis Anagnostopoulos wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I am involved in a heavy database design initiative where the only kind
of geometries I am dealing with are points. I have recently hit a
50million rows long table with those points and my default gist index on
the points does not seem to be working very fast (if not at all to be
honest). I have started now thinking that probably for "points" an index
may not be the best option since in a 50million rows long table most of
the points are unique so the index may just duplicate the actual table,
of course I may be wrong and I may just missing a very important part of
the concept.
</pre>
</blockquote>
<pre wrap="">
How many points from the 50M set does your tipical query hits ?
Does PostgreSQL selectivity estimator make a good guess about that ?
Use EXPLAIN ANALYZE <your query here> to see.
--strk;
() Free GIS & Flash consultant/developer
/\ <a class="moz-txt-link-freetext" href="http://strk.keybit.net/services.html">http://strk.keybit.net/services.html</a>
</pre>
</blockquote>
<br>
</body>
</html>