So, I had a query that uses a postgis geometry index and the planner was
underestimating the number of rows it would return. Because of this,
the planner was choosing the geometry index over a compound index on the
other columns in the WHERE clause. So, I thought, let me increase the
stats target for that geometry column. I did, and I got a different
(and better) plan, but when I looked at the estimates for the simplified
query against the geometry column alone, I noticed that neither the cost
nor the estimated rows changed:
oitest=# explain ANALYZE SELECT * FROM "blips" WHERE
((ST_Contains(blips.shape,
'0101000020E610000049111956F1EB55C0A8E49CD843F34440')) );
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_blips_on_shape_gist on blips (cost=0.00..7.33
rows=1 width=13804) (actual time=0.113..745.394 rows=2827 loops=1)
Index Cond: (shape &&
'0101000020E610000049111956F1EB55C0A8E49CD843F34440'::geometry)
Filter: ((shape &&
'0101000020E610000049111956F1EB55C0A8E49CD843F34440'::geometry) AND
_st_contains(shape,
'0101000020E610000049111956F1EB55C0A8E49CD843F34440'::geometry))
Total runtime: 745.977 ms
(4 rows)
Time: 747.199 ms
oitest=# ALTER TABLE blips ALTER COLUMN shape SET statistics 1000;
ALTER TABLE
Time: 0.478 ms
oitest=# ANALYZE ;
ANALYZE
Time: 7727.097 ms
oitest=# explain ANALYZE SELECT * FROM "blips" WHERE
((ST_Contains(blips.shape,
'0101000020E610000049111956F1EB55C0A8E49CD843F34440')) );
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_blips_on_shape_gist on blips (cost=0.00..7.33
rows=1 width=13761) (actual time=0.117..755.781 rows=2827 loops=1)
Index Cond: (shape &&
'0101000020E610000049111956F1EB55C0A8E49CD843F34440'::geometry)
Filter: ((shape &&
'0101000020E610000049111956F1EB55C0A8E49CD843F34440'::geometry) AND
_st_contains(shape,
'0101000020E610000049111956F1EB55C0A8E49CD843F34440'::geometry))
Total runtime: 756.396 ms
(4 rows)
The width changed slightly, but the cost is 7.33 in both.
So, now I thought how could that have changed the plan? Did the other
parts of the plan estimate change? So I pulled the shape column out of
the where clause and left the others:
oitest=# ALTER TABLE blips ALTER COLUMN shape SET statistics 100;
ALTER TABLE
Time: 0.475 ms
oitest=# ANALYZE ;
ANALYZE
Time: 1225.325 ms
oitest=# explain ANALYZE SELECT * FROM "blips" WHERE
(blips."content_id" = 2410268 AND blips."content_type" = E'Story');
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_blips_on_content_type_and_content_id on blips
(cost=0.00..9.01 rows=2 width=13924) (actual time=0.026..0.027 rows=2
loops=1)
Index Cond: (((content_type)::text = 'Story'::text) AND (content_id =
2410268))
Total runtime: 0.046 ms
(3 rows)
Time: 1.111 ms
oitest=# ALTER TABLE blips ALTER COLUMN shape SET statistics 1000;
ALTER TABLE
Time: 0.506 ms
oitest=# ANALYZE ;
ANALYZE
Time: 7785.496 ms
oitest=# explain ANALYZE SELECT * FROM "blips" WHERE
(blips."content_id" = 2410268 AND blips."content_type" = E'Story');
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_blips_on_content_id on blips (cost=0.00..7.29
rows=1 width=13761) (actual time=0.013..0.014 rows=2 loops=1)
Index Cond: (content_id = 2410268)
Filter: ((content_type)::text = 'Story'::text)
Total runtime: 0.034 ms
(4 rows)
Time: 1.007 ms
So, my question is, should changing the stats target on the shape column
affect the stats for the content_id and content_type columns? Also, why
does the index on content_id win out over the compound index on
(content_type, content_id)?
"index_blips_on_content_id" btree (content_id)
"index_blips_on_content_type_and_content_id" btree (content_type,
content_id)
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
