Hi.
I was wondering how come there is such a drastic difference between finding max and min. Seems like "index scan backwards" is really bad... The table is freshly re-indexed just in case. I added a count(*) in there, forcing the seq scan, and it's even better than the backwards index scan...
db=> EXPLAIN ANALYZE select min(rowdate) from r_agrio where blockid = 4814;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=495.89..495.90 rows=1 width=0) (actual time=24.149..24.150 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..495.89 rows=1 width=13) (actual time=24.139..24.140 rows=1 loops=1)
-> Index Scan using rowdate_r_agrio on r_agrio (cost=0.00..222160.24 rows=448 width=13) (actual time=24.137..24.137 rows=1 loops=1)
Index Cond: ((rowdate)::text IS NOT NULL)
Filter: (blockid = 4814::numeric)
Total runtime: 24.186 ms
(7 rows)
db=> EXPLAIN ANALYZE select max(rowdate) from r_agrio where blockid = 4814;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=495.89..495.90 rows=1 width=0) (actual time=926.032..926.033 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..495.89 rows=1 width=13) (actual time=926.019..926.021 rows=1 loops=1)
-> Index Scan Backward using rowdate_r_agrio on r_agrio (cost=0.00..222160.24 rows=448 width=13) (actual time=926.017..926.017 rows=1 loops=1)
Index Cond: ((rowdate)::text IS NOT NULL)
Filter: (blockid = 4814::numeric)
Total runtime: 926.070 ms
(7 rows)
db=> EXPLAIN ANALYZE select count(*), max(rowdate) from r_agrio where blockid = 4814;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=31585.18..31585.19 rows=1 width=13) (actual time=461.079..461.080 rows=1 loops=1)
-> Seq Scan on r_agrio (cost=0.00..31582.94 rows=448 width=13) (actual time=8.912..460.999 rows=15 loops=1)
Filter: (blockid = 4814::numeric)
Total runtime: 461.134 ms
(4 rows)
db=> \d r_agrio
Table "public.r_agrio"
Column | Type | Modifiers
-------------+-----------------------+--------------------
id | numeric(38,0) | not null
tagid | numeric(38,0) | not null
blockid | numeric(38,0) | not null
rowdate | character varying(15) | not null
count | numeric(38,0) | not null default 0
events | numeric(38,0) | not null default 0
devents | numeric(38,0) | not null default 0
duration | numeric(38,0) | not null default 0
device_type | numeric(38,0) | not null
placement | numeric(38,0) | not null default 0
unserved | numeric(38,0) | not null default 0
unconfirmed | numeric(38,0) | not null default 0
version | numeric(38,0) | not null default 1
Indexes:
"pk_r_agrio" PRIMARY KEY, btree (id)
"u_r_agrio" UNIQUE, btree (tagid, blockid, rowdate, device_type, placement)
"rowdate_r_agrio" btree (rowdate)
