The following bug has been logged on the website:
Bug reference: 15538
Logged by: Vinod TV
Email address: vinviswanath@gmail.com
PostgreSQL version: 9.6.0
Operating system: Windows 10
Description:
Hi, We have created the table using table inheritance as we are storing
large number of data for years. When we query for one year data it tooks
more than 25 secs time, but expectation was within 2 sec.
Please let me know if i am doing something wrong here, please find the
details.
Query Used.
select
*
from
"public"."GroupedSummaryData_G37852_T5_GS1H"
where
"id" = 38144 and
"time" >= '10/31/2017' and
"time" <= '10/28/2018'
and "isValid" = true
order by
"time" asc
EXPLAIN ANALYZE
Sort (cost=126.08..126.16 rows=30 width=221) (actual
time=20624.548..20625.344 rows=10530 loops=1)
Sort Key: "GroupedSummaryData_G4398_T5_GS1H"."time"
Sort Method: quicksort Memory: 1866kB
->Append (cost=0.00..125.35 rows=30 width=221) (actual
time=394.207..20614.899 rows=10530 loops=1)
-> Seq Scan on "GroupedSummaryData_G4398_T5_GS1H" (cost=0.00..0.00
rows=1 width=221) (actual time=0.004..0.004 rows=0 loops=1)
Filter: ("isValid" AND ("time" >= '2017-10-31
00:00:00'::timestamp without time zone) AND ("time" <= '2018-10-28
00:00:00'::timestamp without time zone) AND (id = 25602))
-> Bitmap Heap Scan on "GroupedSummaryData_G4398_T5_GS1H_2018"
(cost=4.80..56.63 rows=13 width=221) (actual time=394.201..17563.783
rows=8661 loops=1)
Recheck Cond: ((id = 25602) AND ("time" >= '2017-10-31
00:00:00'::timestamp without time zone) AND ("time" <= '2018-10-28
00:00:00'::timestamp without time zone))
Heap Blocks: exact=8382
Filter: "isValid"
-> Bitmap Index Scan on
index_groupedsummarydata_g4398_t5_gs1h_2018 (cost=0.00..4.80 rows=13
width=0) (actual time=387.576..387.576 rows=8661 loops=1)
Index Cond: ((id = 25602) AND ("time" >= '2017-10-31
00:00:00'::timestamp without time zone) AND ("time" <= '2018-10-28
00:00:00'::timestamp without time zone) AND ("isValid" = true))
-> Bitmap Heap Scan on "GroupedSummaryData_G4398_T5_GS1H_2017"
(cost=4.91..68.72 rows=16 width=221) (actual time=89.143..3048.801 rows=1869
loops=1)
Recheck Cond: ((id = 25602) AND ("time" >= '2017-10-31
00:00:00'::timestamp without time zone) AND ("time" <= '2018-10-28
00:00:00'::timestamp without time zone))
Filter: "isValid"
Heap Blocks: exact=1810
-> Bitmap Index Scan on
index_groupedsummarydata_g4398_t5_gs1h_2017 (cost=0.00..4.91 rows=16
width=0) (actual time=84.087..84.087 rows=1869 loops=1)
Index Cond: ((id = 25602) AND ("time" >= '2017-10-31
00:00:00'::timestamp without time zone) AND ("time" <= '2018-10-28
00:00:00'::timestamp without time zone) AND ("isValid" = true))
Planning time: 133.686 ms
Execution time: 20625.940 ms
Table structure
CREATE TABLE public."GroupedSummaryData_G4398_T5"
(
id integer,
"time" timestamp without time zone,
"isValid" boolean,
"summaryCount" integer,
"dataStatusesInfo" smallint,
"nodeStatusesInfo" smallint,
"countsInfo" smallint,
"timesInfo" smallint,
"valuesInfo" smallint,
"averagesInfo" smallint,
"dataStatuses" bytea,
"nodeStatuses" bytea,
counts bytea,
times bytea,
"values" bytea,
averages bytea
)
INHERITS (public."GroupedSummaryData_G4398")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
Every table is having index on id, time, isValid
CREATE INDEX index_groupedsummarydata_g4398_t5_gs1h_2017
ON public."GroupedSummaryData_G4398_T5_GS1H_2017" USING btree
(id, time, isValid)
TABLESPACE pg_default;
CREATE INDEX index_groupedsummarydata_g4398_t5_gs1h_2018
ON public."GroupedSummaryData_G4398_T5_GS1H_2018" USING btree
(id, time, isValid)
TABLESPACE pg_default;
INFO: analyzing "public.GroupedSummaryData_G4398_T5"
INFO: "GroupedSummaryData_G4398_T5": scanned 0 of 0 pages, containing 0
live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO: analyzing "public.GroupedSummaryData_G4398_T5" inheritance tree
INFO: "GroupedSummaryData_G4398_T5_GS1H_2018": scanned 4935 of 31594 pages,
containing 285971 live rows and 0 dead rows; 4935 rows in sample, 1830794
estimated total rows
INFO: "GroupedSummaryData_G4398_T5_GS1H_2014": scanned 6287 of 40246 pages,
containing 365188 live rows and 0 dead rows; 6287 rows in sample, 2337738
estimated total rows
INFO: "GroupedSummaryData_G4398_T5_GS1H_2015": scanned 6340 of 40585 pages,
containing 370990 live rows and 0 dead rows; 6340 rows in sample, 2374863
estimated total rows
INFO: "GroupedSummaryData_G4398_T5_GS1H_2016": scanned 6116 of 39151 pages,
containing 357613 live rows and 6 dead rows; 6116 rows in sample, 2289226
estimated total rows
INFO: "GroupedSummaryData_G4398_T5_GS1H_2017": scanned 6322 of 40478 pages,
containing 370938 live rows and 0 dead rows; 6322 rows in sample, 2375012
estimated total rows
ANALYZE
Query returned successfully in 1 min.
