From: | Pawel Veselov <pawel(dot)veselov(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | min/max performance inequality. |
Date: | 2015-01-07 23:00:02 |
Message-ID: | CAMnJ+Bf7uGJNLfwpomBO6pzywqvRj9yU4Daahs3=itZpmxbELg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2015-01-07 23:26:20 | Re: ALTER TABLE to ADD BDR global sequence |
Previous Message | deepak | 2015-01-07 21:08:30 | How to exclude building/installing contrib modules on Windows |