From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view.
Since it's a view, I pretend to calculate the sum for every single row in the table, and then I was hoping that the optimizer would realize when I am only asking for a single row and would optimize the query. However my query plan is massive, and is calculating 17 billion rows at its innermost point, where I think there should be at most a thousand.
Here is the query:
CREATE OR REPLACE VIEW weighted_stats AS WITH clf AS (SELECT * FROM classifiers order by time_trained desc limit 1), weights AS (SELECT kv.key, kv.value from clf, each(clf.weights) AS kv), kvs AS ( SELECT stats.player_id, kv.key, kv.value FROM stats, each(stats.hstore_column) AS kv), SELECT stats.player_id, SUM(kvs.value :: numeric * weights.value :: numeric) AS stats FROM kvs JOIN weights USING (key) GROUP BY kvs.player_id;
This is the query plan:
explain analyze select * from weighted_stats where player_id=76561197960269296 GroupAggregate (cost=53645.35..299471.72 rows=1 width=72) (actual time=1014.016..1014.016 rows=0 loops=1) Group Key: kvs.id CTE clf -> Limit (cost=20.65..20.65 rows=1 width=84) (actual time=0.017..0.018 rows=1 loops=1) -> Sort (cost=20.65..22.43 rows=710 width=84) (actual time=0.014..0.014 rows=1 loops=1) Sort Key: classifiers.time_trained Sort Method: quicksort Memory: 25kB -> Seq Scan on classifiers (cost=0.00..17.10 rows=710 width=84) (actual time=0.003..0.005 rows=1 loops=1) CTE kvs -> Seq Scan on stats (cost=0.00..53572.18 rows=10318000 width=722) (actual time=0.037..530.337 rows=336036 loops=1) CTE weights -> Nested Loop (cost=0.00..20.02 rows=1000 width=64) (actual time=0.036..0.046 rows=2 loops=1) -> CTE Scan on clf (cost=0.00..0.02 rows=1 width=32) (actual time=0.020..0.023 rows=1 loops=1) -> Function Scan on each kv (cost=0.00..10.00 rows=1000 width=64) (actual time=0.011..0.013 rows=2 loops=1) -> Hash Join (cost=32.50..241344.73 rows=257950 width=72) (actual time=1014.012..1014.012 rows=0 loops=1) Hash Cond: (kvs.key = weights.key) -> CTE Scan on kvs (cost=0.00..232155.00 rows=51590 width=72) (actual time=0.044..1013.877 rows=62 loops=1) Filter: (id = 76561197960269296::bigint) Rows Removed by Filter: 335974 -> Hash (cost=20.00..20.00 rows=1000 width=64) (actual time=0.060..0.060 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> CTE Scan on weights (cost=0.00..20.00 rows=1000 width=64) (actual time=0.040..0.054 rows=2 loops=1) Planning time: 0.286 ms Execution time: 1017.671 ms
This is still a lot slower than what I'd expect. The optimization is working partially by filtering before the join, instead of before the group by, but it seems like the kvs CTE (which should itself be filtered) is still being calculated for everyone.