Hi Peter,
To build on the other answers you've received - you need to create indexes that
the database will use. This applies to all relational databases - Oracle/SQL
Server/PostGres/Sqlite, etc.
When you send a query to a database there's a query planner which takes your
query and tries to determine the optimal way to execute it. It looks at what
indexes you have and tries to decide which, if any, will get it the answer the
fastest. Sometimes it will decide an index won't help.
Just having an index on a particular column (or set of columns) doesn't mean
the database will use it.
Some reading around the topic:
http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm
(Note: Different databases have different planners and work differently -
that's all Oracle specific).
I'd suggest taking the query that is slow for you, and trying to optimise the
database around it by using the EXPLAIN function and seeing where it's spending
all of its time.
While Andrea has an (understandable) leaning towards PostGres, I'd be surprised
if you can't optimise Oracle to get the desired result too if you're fixed with
that (but if you're not - PostGres would probably be a worthwhile change).
Cheers,
Jonathan
---- On Wed, 22 Jun 2016 09:33:57 +0100 Andrea
Aime<andrea.a...@geo-solutions.it> wrote ----
On Wed, Jun 22, 2016 at 10:02 AM, Peter Kovac
<peter.ko...@microstep-mis.com> wrote:
Hi Andrea,
what do you mean with "deciding which one to use"? AFAIK it has to use both,
(almost) all the time.
According to what Bruce Momjan told me a few years ago, not always. He was
going over some statistics
on index usage, and when it's actually more efficient to use an index than to
do a linear scan, and the
threshold was less than 10% of the data (can't remember the actual value), in
other terms, index access is faster
than a linear scan in postgresql only if the index selects less than 10% of the
overall data.
Based on that, the planner will decide to use no index, only one, or both,
based on their selectivity.
In your case it seems you're constraining much more on time than on space, if
that's the case there is a chance
only the time index will be used, because using the other one as well might
result in slower execution.
This is the query found in GeoServer logs (LOCATION is the point geometry
column with a spatial index):
SELECT ID,TIME,LOCATION
FROM lightning_strikes
WHERE (
TIME BETWEEN ? AND ?
AND SDO_FILTER(LOCATION, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE')
)
Let's say I want just 1 hour worth of lightning strikes but from almost all of
the central Europe (so the BBOX is not covering the whole dataset but covers
90% of it).
The 'TIME BETWEEN' clause quickly filters 3M rows to let's say 10K lightnings.
However, some of them lie outside of the BBOX. So it has to filter them out.
Unfortunately, there are millions of other records in the same BBOX, so the
spatial index finds them all and then the database engine has to make
intersection of the two sets. Since one of them is huge, it takes time. And it
will get worse over time.
Yes!
OK, maybe I see your point now. The DB engine could just check each row from
the smaller set if it is inside the BBOX, without using the spatial index. I
wonder if PostGIS can do it. I'll give it a try.
In my experience it does. Let us know how it goes.
Cheers
Andrea
--
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i
file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo
è consentito esclusivamente al destinatario del messaggio, per le finalità
indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne
il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di
procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro
sistema. Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse,
costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for the
attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act (Legislative
Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in
accord with its purpose, any disclosure, reproduction, copying, distribution,
or either dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the intended
recipient, please contact immediately the sender by telephone, fax or e-mail
and delete the information in this message that has been received in error. The
sender does not give any warranty or accept liability as the content, accuracy
or completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.
-------------------------------------------------------
------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
