[Please CC me on replies, as I'm not subscribed; thank you.]
I've ran into a problem with the query planner and IN (subquery)
conditions which I suspect to be a bug. I'll attempt to describe the
relevant details of my database and explain which behaviour I find
unexpected. I've also tried to trigger this behaviour in a clean
database; I think I've succeeded, but the conditions are a bit
different, so perhaps it's a different problem. I'll describe this
setup in detail below.
I have a somewhat large table (~2.5M rows), stats, which is quite
often (several records a minute) INSERTed to, but never UPDATEd or
DELETEd from. (In case it's relevant, it has an attached AFTER INSERT
trigger which checks time and rebuilds an aggregate materialized view
every hour.) This is the schema:
# \d+ stats
Table "serverwatch.stats"
Column |Type |
Modifiers | Storage | Description
--+-++-+-
id | integer | not null default
nextval('stats_id_seq'::regclass) | plain |
run_id | integer | not null
| plain |
start_time | timestamp without time zone | not null
| plain |
end_time | timestamp without time zone | not null
| plain |
cpu_utilization | double precision|
| plain |
disk_read_ops| bigint |
| plain |
disk_write_ops | bigint |
| plain |
network_out | bigint |
| plain |
network_in | bigint |
| plain |
disk_read_bytes | bigint |
| plain |
disk_write_bytes | bigint |
| plain |
Indexes:
"stats_pkey" PRIMARY KEY, btree (id)
"stats_day_index" btree (run_id, day(stats.*))
"stats_month_index" btree (run_id, month(stats.*))
"stats_week_index" btree (run_id, week(stats.*))
Foreign-key constraints:
"stats_runs" FOREIGN KEY (run_id) REFERENCES runs(id)
Triggers:
stats_day_refresh_trigger AFTER INSERT OR UPDATE ON stats FOR EACH
STATEMENT EXECUTE PROCEDURE mat_view_refresh('serverwatch.stats_day')
Has OIDs: no
day(), month() and week() functions are just trivial date_trunc on a
relevant field. The referenced table looks like this:
# \d+ runs
Table "serverwatch.runs"
Column |Type |
Modifiers | Storage | Description
-+-+---+-+-
id | integer | not null default
nextval('runs_id_seq'::regclass) | plain |
server_id | integer | not null
| plain |
flavor | flavor | not null
| plain |
region | region | not null
| plain |
launch_time | timestamp without time zone | not null
| plain |
stop_time | timestamp without time zone |
| plain |
project_info_id | integer | not null
| plain |
owner_info_id | integer | not null
| plain |
Indexes:
"runs_pkey" PRIMARY KEY, btree (id)
"index_runs_on_flavor" btree (flavor)
"index_runs_on_owner_info_id" btree (owner_info_id)
"index_runs_on_project_info_id" btree (project_info_id)
"index_runs_on_region" btree (region)
"index_runs_on_server_id" btree (server_id)
Foreign-key constraints:
"runs_owner_info_id_fkey" FOREIGN KEY (owner_info_id) REFERENCES
user_infos(id)
"runs_project_info_id_fkey" FOREIGN KEY (project_info_id)
REFERENCES project_infos(id)
Referenced by:
TABLE "stats_day" CONSTRAINT "stats_day_runs" FOREIGN KEY (run_id)
REFERENCES runs(id)
TABLE "stats" CONSTRAINT "stats_runs" FOREIGN KEY (run_id)
REFERENCES runs(id)
Has OIDs: no
Now consider this query - note I'm using a subselect here because the
problem originally manifested itself with a view:
SELECT * FROM (SELECT run_id, disk_write_ops FROM stats) AS s WHERE
run_id IN (SELECT id FROM runs WHERE server_id = 515);
As might be expected, the planner chooses to use one of the three
indices with run_id:
http://explain.depesz.com/s/XU3Q
Now consider a similar query, but with aggregation:
SELECT * FROM (SELECT run_id, SUM(d
