0

Schema

create table soa_rawdata_lp
(
    date_created timestamp with time zone default now(),
    filename     varchar(128),
    xmldata      xml
);

Each xmldata entry is about 4.2MB size. Currently there are 500 records in the table.

Query

    select  distinct x1.*
    from soa_rawdata_lp,
         xmltable(
             XMLNAMESPACES('http://www.emeter.com/energyip/amiinterface' as x),
             '/x:MeterReadsReplyMessage/x:payload/x:MeterReading/x:IntervalBlock/x:IReading' passing xmldata
             columns
                mrid text path '../../x:Meter/x:mRID',
                end_time timestamptz path 'x:endTime',
                reading_type_id text path '../x:readingTypeId'

             ) as x1
    where mrid = '4012564103' and end_time = '2019-10-10 05:15:00'
    order by x1.mrid, x1.end_time;

### Explain analyze:

rawdata_db=> explain (analyze,buffers) select  distinct x1.*
rawdata_db-> from soa_rawdata_lp,
rawdata_db->      xmltable(
rawdata_db(>          XMLNAMESPACES('http://www.emeter.com/energyip/amiinterface' as x),
rawdata_db(>          '/x:MeterReadsReplyMessage/x:payload/x:MeterReading/x:IntervalBlock/x:I
Reading' passing xmldata
rawdata_db(>          columns
rawdata_db(>             mrid text path '../../x:Meter/x:mRID',
rawdata_db(>             end_time timestamptz path 'x:endTime',
rawdata_db(>             reading_type_id text path '../x:readingTypeId'
rawdata_db(>
rawdata_db(>          ) as x1
rawdata_db-> where mrid = '4012564103'  and end_time = '2019-10-10 05:15:00'
rawdata_db-> order by x1.mrid, x1.end_time;
                                                               QUERY PLAN                     
-----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=633.72..633.72 rows=1 width=72) (actual time=46923.953..46923.954 rows=16 loops=1)
   Sort Key: x1.end_time
   Sort Method: quicksort  Memory: 27kB
   Buffers: shared hit=19084
   ->  HashAggregate  (cost=633.70..633.71 rows=1 width=72) (actual time=46923.900..46923.904 rows=16 loops=1)
         Group Key: x1.mrid, x1.end_time, x1.reading_type_id
         Buffers: shared hit=19081
         ->  Nested Loop  (cost=0.00..630.03 rows=489 width=72) (actual time=8806.799..46923.853 rows=16 loops=1)
               Buffers: shared hit=19081
               ->  Seq Scan on soa_rawdata_lp  (cost=0.00..13.89 rows=489 width=18) (actual time=0.007..1.595 rows=500 loops=1)
                     Buffers: shared hit=9
               ->  Table Function Scan on "xmltable" x1  (cost=0.00..1.25 rows=1 width=72) (actual time=93.761..93.762 rows=0 loops=500)
                     Filter: (mrid = '4012564103'::text)
                     Rows Removed by Filter: 7975
                     Buffers: shared hit=19072
 Planning Time: 0.197 ms
 Execution Time: 46924.195 ms
(17 rows)

Question

If I want to create index for the column mrid and end_time, what command should I input?

My target is to reduce the execution time to below 10 seconds. Besides creating an index, are there any other suggestion?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.