Re: difference in query performance due to the inclusion of a polygon geometry field

ivan marchesini wrote:
> we are facing with a strange efficiency problem.
> 
> We have two tables:
> 1) the first largest (30000 rows) contains a timestamp field (named
> "tempo_calcolo") and other fields.
> 2) the second contains only few rows (150), a PostGIS geometric POLYGON
> field (named "the_geom") and other fields.
> 
> The query we want to execute is a join between the two table based on a
> common field, filtered by the timestamp field and ordered by the same
> field.
> 
> Including and excluding the PostGIS geometric POLYGON field (that is not
> used neither in the joining or in the filtering) the execution times are
> really different.
> 
> Without the geometric field it takes around 86 ms !!
> With the geometric field it takes around 14000 ms !!
> 
> These are the EXPLAIN ANALYSE results obtained when we perform the query
> without the geometric field:
> ____________________________
> "Sort  (cost=1217.29..1240.40 rows=9246 width=133) (actual
> time=2.964..3.095 rows=1474 loops=1)"
> "  Sort Key: storico_zone_allertamento.tempo_calcolo"
> "  Sort Method:  quicksort  Memory: 440kB"
> "  InitPlan 1 (returns $0)"
> "    ->  Result  (cost=0.00..0.02 rows=1 width=0) (actual
> time=0.022..0.022 rows=1 loops=1)"
> "  ->  Hash Join  (cost=6.01..608.21 rows=9246 width=133) (actual
> time=0.143..1.916 rows=1474 loops=1)"
> "        Hash Cond: (storico_zone_allertamento.gid_fk =
> zone_allertamento.gid)"
> "        ->  Index Scan using
> index_storico_zone_allertamento_tempo_calcolo on
> storico_zone_allertamento  (cost=0.00..475.06 rows=9246 width=112)
> (actual time=0.042..0.440 rows=1474 loops=1)"
> "              Index Cond: (tempo_calcolo > $0)"
> "        ->  Hash  (cost=4.34..4.34 rows=134 width=25) (actual
> time=0.090..0.090 rows=134 loops=1)"
> "              Buckets: 1024  Batches: 1  Memory Usage: 8kB"
> "              ->  Seq Scan on zone_allertamento  (cost=0.00..4.34
> rows=134 width=25) (actual time=0.004..0.035 rows=134 loops=1)"
> "Total runtime: 3.285 ms"
> __________________________
> 
> 
> These are the EXPLAIN ANALYSE results obtained when we perform the query
> with the geometric field:
> ___________________________
> "Sort  (cost=776019.29..776042.40 rows=9246 width=196111) (actual
> time=3.039..3.163 rows=1474 loops=1)"
> "  Sort Key: storico_zone_allertamento.tempo_calcolo"
> "  Sort Method:  quicksort  Memory: 567kB"
> "  InitPlan 1 (returns $0)"
> "    ->  Result  (cost=0.00..0.02 rows=1 width=0) (actual
> time=0.009..0.009 rows=1 loops=1)"
> "  ->  Hash Join  (cost=6.01..608.21 rows=9246 width=196111) (actual
> time=0.139..1.957 rows=1474 loops=1)"
> "        Hash Cond: (storico_zone_allertamento.gid_fk =
> zone_allertamento.gid)"
> "        ->  Index Scan using
> index_storico_zone_allertamento_tempo_calcolo on
> storico_zone_allertamento  (cost=0.00..475.06 rows=9246 width=112)
> (actual time=0.029..0.431 rows=1474 loops=1)"
> "              Index Cond: (tempo_calcolo > $0)"
> "        ->  Hash  (cost=4.34..4.34 rows=134 width=196003) (actual
> time=0.091..0.091 rows=134 loops=1)"
> "              Buckets: 1024  Batches: 1  Memory Usage: 19kB"
> "              ->  Seq Scan on zone_allertamento  (cost=0.00..4.34
> rows=134 width=196003) (actual time=0.006..0.038 rows=134 loops=1)"
> "Total runtime: 3.355 ms"
> __________________________
> 
> 
> It seems that the difference is primary in the "width" and then in the
> "cost" of the Sort.
> 
> A similar task performed on two similar tables (both larger than the
> previous ones!!) but including and excluding a PostGIS geometric POINT
> (!!) field instead of POLYGON one, doesn't result in significant query
> execution time difference.
> 
> Do you have please any suggestion to improve our query efficiency??

That's strange.

The plans that you posted suggest that query execution time is
almost identical in both cases (3.285 vs. 3.355 milliseconds).

How do you measure the execution time?

If it is a measurement on the client side, I'd suppose that
the time is consumed by the transfer of the data to the client
or the processing on the client.

Yours,
Laurenz Albe