Обсуждение: BUG #15538: Postgres query performance is slow.

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.

I don't see evidence of a bug here (so far), but a couple of things are
worthy of mention:

-  The estimated number of rows is much higher than the actual.  A likely
cause of this under-estimation
   is that the columns are correlated.  If so, then the estimate may be
improved in PG10+ by collecting
   multi-column statistics on (id, time) or (id, time, isValid), but that
doesn't help you on PG9.6
-  For the historic partitions that are no longer being updated, you could
cluster the heap by the index,
   and possibly disable the bitmap heap scan.  If either or both of these
changes improve scan performance
   significantly on the 2017 data, consider partitioning by month and
clustering historic monthly partitions.



-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html