From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Forø Tollefsen <andreasft@gmail.com>
Cc: Oleg Bartunov <oleg@sai.msu.su>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Mon, 07 Mar 2011 19:38:41 -0500
Message-ID: <18812.1299544721@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTik1Nkq-pRb7OEFDRzDPA8147rTrkh8as=ALRtm2@mail.gmail.com>
References: <AANLkTinZkVapdjrZULpc8zmwb4w8wotkYzj0gqj_jPXb@mail.gmail.com>
<20110307140101.GL8169@aart.is.rice.edu>
<AANLkTim71gwPa3y0Sggdzqh+-m=DY7-NWNe-BE7XwwPb@mail.gmail.com>
<20110307142216.GM8169@aart.is.rice.edu>
<AANLkTim4RoKaQcBWhjer34a0EjSX-PWQkoXGYV0YY=kM@mail.gmail.com>
<Pine.LNX.4.64.1103071834040.14517@sn.sai.msu.ru>
<AANLkTik1Nkq-pRb7OEFDRzDPA8147rTrkh8as=ALRtm2@mail.gmail.com>
=?ISO-8859-1?Q?Andreas_For=F8_Tollefsen?= <andreasft@gmail.com> writes:
> This is a query i am working on now. It creates an intersection of two
> geometries. One is a grid of 0.5 x 0.5 decimal degree sized cells, while the
> other is the country geometries of all countries in the world for a certain
> year.
Hm, are you sure your data is right? Because the actual rowcounts imply
that each country intersects about half of the grid cells, which doesn't
seem right.
> priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode,
> ST_Intersection(pri
> ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land, cshapeswdate WHERE
> ST_Intersects(priogrid_land.cell, cshapeswdate.geom);
> QUERY
> PLAN
> --------------------------------------------------------------------------------
> ------------------------------------------------------------------
> Nested Loop (cost=0.00..12644.85 rows=43351 width=87704) (actual
> time=1.815..7
> 074973.711 rows=130331 loops=1)
> Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)
> -> Seq Scan on cshapeswdate (cost=0.00..14.42 rows=242 width=87248)
> (actual
> time=0.007..0.570 rows=242 loops=1)
> -> Index Scan using idx_priogrid_land_cell on priogrid_land
> (cost=0.00..7.1
> 5 rows=1 width=456) (actual time=0.069..5.604 rows=978 loops=242)
> Index Cond: (priogrid_land.cell && cshapeswdate.geom)
> Total runtime: 7075188.549 ms
> (6 rows)
AFAICT, all of the runtime is going into calculating the ST_Intersects
and/or ST_Intersection functions. The two scans are only accounting for
perhaps 5.5 seconds, and the join infrastructure isn't going to be
terribly expensive, so it's got to be those functions. Not knowing much
about PostGIS, I don't know if the functions themselves can be expected
to be really slow. If it's not them, it could be the cost of fetching
their arguments --- in particular, I bet the country outlines are very
large objects and are toasted out-of-line. There's been some past
discussion of automatically avoiding repeated detoastings in scenarios
like the above, but nothing's gotten to the point of acceptance yet.
Possibly you could do something to force detoasting in a subquery.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, andreasft@gmail.com, oleg@sai.msu.su, ktm@rice.edu
Subject: Re: Performance issues
In-Reply-To: <18812.1299544721@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox