Re: Strange issue with GiST index scan taking far too long

From: Mark Cave-Ayland <mark(dot)cave-ayland(at)siriusit(dot)co(dot)uk>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Strange issue with GiST index scan taking far too long
Date: 2008-06-09 14:44:52
Message-ID: 484D41E4.5050903@siriusit.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Simon Riggs wrote:

> Hmmm, perhaps implicit casting?
>
> Try this to see if it works better also
>
> select count(*) from geography where centroid
> && (select the_geom::geometry from geography where id=69495);

Hi Simon,

Unfortunately that seems to take the slow runtime path too. I did
initially think about casting being involved (since the underlying index
storage type is actually box2d rather than geometry), however my
mcatest() function is also declared as returning geometry too.

Interesting enough, forcing a cast to box2d instead of geometry seems to
take the faster path, i.e:

postgis=# explain analyze select count(*) from geography where centroid
&& (select the_geom::box2d from geography where id=69495);

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7157.29..7157.30 rows=1 width=0) (actual
time=376.033..376.034 rows=1 loops=1)
InitPlan
-> Seq Scan on geography (cost=0.00..7149.00 rows=1 width=4559)
(actual time=42.853..43.051 rows=1 loops=1)
Filter: (id = 69495::numeric)
-> Index Scan using geography_geom_centroid_idx on geography
(cost=0.00..8.28 rows=1 width=0) (actual time=43.218..286.535 rows=32880
loops=1)
Index Cond: (centroid && ($0)::geometry)
Filter: (centroid && ($0)::geometry)
Total runtime: 376.117 ms
(8 rows)

ATB,

Mark.

--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-06-09 14:57:12 Re: pg_dump restore time and Foreign Keys
Previous Message Tom Lane 2008-06-09 14:38:49 Re: Message-ID should surely not be shown as a mailto: URL