Skip to main content
Database Administrators

Return to Question

added [query-performance] to 2412 questions - Shog9 (Id=1924)
Link
Community Bot
Community Bot
  • 1
edited body
Source Link
Ray A.
Ray A.
  • 101
  • 2

I currently have a table with around 300,000,000 rows with the following columns: user_id, item_id, item_value, and item_add_date

I also have a very long list of about 7000 user_id values which are associated with a date range.

I'm trying to query the really large table in order to obtain all rows where the user_id is in my list and where the item_add_date falls within the provided range.

My current approach is programatically looping through the list of ids and running the following query:

FROM all_items 
WHERE user_id in [insert_user_id_programatically] 
AND item_add_date >= [insert_start_date] 
AND item_add_date <= [insert_end_date]

which returns the results I want but takes about one hour per query, and for 7000 queries would take almost a year to run. Is there a faster way of achieving this? I'm not familiar with SQL and do most of the data manipulation for this project in python normally.

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE): https://pastebin.com/h8YXptckhttps://pastebin.com/GYL8JWk4

I currently have a table with around 300,000,000 rows with the following columns: user_id, item_id, item_value, and item_add_date

I also have a very long list of about 7000 user_id values which are associated with a date range.

I'm trying to query the really large table in order to obtain all rows where the user_id is in my list and where the item_add_date falls within the provided range.

My current approach is programatically looping through the list of ids and running the following query:

FROM all_items 
WHERE user_id in [insert_user_id_programatically] 
AND item_add_date >= [insert_start_date] 
AND item_add_date <= [insert_end_date]

which returns the results I want but takes about one hour per query, and for 7000 queries would take almost a year to run. Is there a faster way of achieving this? I'm not familiar with SQL and do most of the data manipulation for this project in python normally.

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE): https://pastebin.com/h8YXptck

I currently have a table with around 300,000,000 rows with the following columns: user_id, item_id, item_value, and item_add_date

I also have a very long list of about 7000 user_id values which are associated with a date range.

I'm trying to query the really large table in order to obtain all rows where the user_id is in my list and where the item_add_date falls within the provided range.

My current approach is programatically looping through the list of ids and running the following query:

FROM all_items 
WHERE user_id in [insert_user_id_programatically] 
AND item_add_date >= [insert_start_date] 
AND item_add_date <= [insert_end_date]

which returns the results I want but takes about one hour per query, and for 7000 queries would take almost a year to run. Is there a faster way of achieving this? I'm not familiar with SQL and do most of the data manipulation for this project in python normally.

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE): https://pastebin.com/GYL8JWk4

deleted 15287 characters in body
Source Link
Ray A.
Ray A.
  • 101
  • 2

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE):

[('Append  (cost=0.00..10081481.47 rows=18 width=241) (actual time=0.076..699631.307 rows=6588 loops=1)',), ('  Buffers: shared hit=119402 read=4174611',), ('  ->  Seq Scan on mimiciii.chartevents  (cost=0.00..0.00 rows=1 width=926) (actual time=0.002..0.002 rows=0 loops=1)',), ('        Output: chartevents.row_id, chartevents.subject_id, chartevents.hadm_id, chartevents.icustay_id, chartevents.itemid, chartevents.charttime, chartevents.storetime, chartevents.cgid, chartevents.value, chartevents.valuenum, chartevents.valueuom, chartevents.warning, chartevents.error, chartevents.resultstatus, chartevents.stopped',), ("        Filter: ((chartevents.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents.icustay_id = 231756))",), ('  ->  Seq Scan on mimiciii.chartevents_1  (cost=0.00..560697.60 rows=1 width=83) (actual time=0.073..63067.428 rows=524 loops=1)',), ('        Output: chartevents_1.row_id, chartevents_1.subject_id, chartevents_1.hadm_id, chartevents_1.icustay_id, chartevents_1.itemid, chartevents_1.charttime, chartevents_1.storetime, chartevents_1.cgid, chartevents_1.value, chartevents_1.valuenum, chartevents_1.valueuom, chartevents_1.warning, chartevents_1.error, chartevents_1.resultstatus, chartevents_1.stopped',), ("        Filter: ((chartevents_1.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_1.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_1.icustay_id = 231756))",), ('        Rows Removed by Filter: 18385796',), ('        Buffers: shared hit=484 read=238453',), ('  ->  Seq Scan on mimiciii.chartevents_2  (cost=0.00..594339.70 rows=1 width=196) (actual time=57788.561..66593.194 rows=1043 loops=1)',), ('        Output: chartevents_2.row_id, chartevents_2.subject_id, chartevents_2.hadm_id, chartevents_2.icustay_id, chartevents_2.itemid, chartevents_2.charttime, chartevents_2.storetime, chartevents_2.cgid, chartevents_2.value, chartevents_2.valuenum, chartevents_2.valueuom, chartevents_2.warning, chartevents_2.error, chartevents_2.resultstatus, chartevents_2.stopped',), ("        Filter: ((chartevents_2.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_2.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_2.icustay_id = 231756))",), ('        Rows Removed by Filter: 19646198',), ('        Buffers: shared hit=329 read=250184',), ('  ->  Seq Scan on mimiciii.chartevents_3  (cost=0.00..637061.66 rows=1 width=194) (actual time=53381.363..74843.690 rows=548 loops=1)',), ('        Output: chartevents_3.row_id, chartevents_3.subject_id, chartevents_3.hadm_id, chartevents_3.icustay_id, chartevents_3.itemid, chartevents_3.charttime, chartevents_3.storetime, chartevents_3.cgid, chartevents_3.value, chartevents_3.valuenum, chartevents_3.valueuom, chartevents_3.warning, chartevents_3.error, chartevents_3.resultstatus, chartevents_3.stopped',), ("        Filter: ((chartevents_3.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_3.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_3.icustay_id = 231756))",), ('        Rows Removed by Filter: 20927604',), ('        Buffers: shared hit=406 read=270413',), ('  ->  Seq Scan on mimiciii.chartevents_4  (cost=0.00..587540.59 rows=1 width=195) (actual time=46562.302..59885.102 rows=1047 loops=1)',), ('        Output: chartevents_4.row_id, chartevents_4.subject_id, chartevents_4.hadm_id, chartevents_4.icustay_id, chartevents_4.itemid, chartevents_4.charttime, chartevents_4.storetime, chartevents_4.cgid, chartevents_4.value, chartevents_4.valuenum, chartevents_4.valueuom, chartevents_4.warning, chartevents_4.error, chartevents_4.resultstatus, chartevents_4.stopped',), ("        Filter: ((chartevents_4.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_4.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_4.icustay_id = 231756))",), ('        Rows Removed by Filter: 19128587',), ('        Buffers: shared hit=405 read=252367',), ('  ->  Seq Scan on mimiciii.chartevents_5  (cost=0.00..661280.41 rows=1 width=193) (actual time=55998.470..74354.293 rows=1041 loops=1)',), ('        Output: chartevents_5.row_id, chartevents_5.subject_id, chartevents_5.hadm_id, chartevents_5.icustay_id, chartevents_5.itemid, chartevents_5.charttime, chartevents_5.storetime, chartevents_5.cgid, chartevents_5.value, chartevents_5.valuenum, chartevents_5.valueuom, chartevents_5.warning, chartevents_5.error, chartevents_5.resultstatus, chartevents_5.stopped',), ("        Filter: ((chartevents_5.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_5.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_5.icustay_id = 231756))",), ('        Rows Removed by Filter: 21678411',), ('        Buffers: shared hit=396 read=281494',), ('  ->  Seq Scan on mimiciii.chartevents_6  (cost=0.00..541249.00 rows=1 width=195) (actual time=34122.552..37744.519 rows=698 loops=1)',), ('        Output: chartevents_6.row_id, chartevents_6.subject_id, chartevents_6.hadm_id, chartevents_6.icustay_id, chartevents_6.itemid, chartevents_6.charttime, chartevents_6.storetime, chartevents_6.cgid, chartevents_6.value, chartevents_6.valuenum, chartevents_6.valueuom, chartevents_6.warning, chartevents_6.error, chartevents_6.resultstatus, chartevents_6.stopped',), ("        Filter: ((chartevents_6.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_6.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_6.icustay_id = 231756))",), ('        Rows Removed by Filter: 17611302',), ('        Buffers: shared hit=398 read=232641',), ('  ->  Seq Scan on mimiciii.chartevents_7  (cost=0.00..634900.51 rows=1 width=78) (actual time=29254.283..33246.252 rows=920 loops=1)',), ('        Output: chartevents_7.row_id, chartevents_7.subject_id, chartevents_7.hadm_id, chartevents_7.icustay_id, chartevents_7.itemid, chartevents_7.charttime, chartevents_7.storetime, chartevents_7.cgid, chartevents_7.value, chartevents_7.valuenum, chartevents_7.valueuom, chartevents_7.warning, chartevents_7.error, chartevents_7.resultstatus, chartevents_7.stopped',), ("        Filter: ((chartevents_7.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_7.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_7.icustay_id = 231756))",), ('        Rows Removed by Filter: 20702251',), ('        Buffers: shared hit=403 read=272192',), ('  ->  Seq Scan on mimiciii.chartevents_8  (cost=0.00..661907.71 rows=1 width=79) (actual time=31421.236..35013.673 rows=384 loops=1)',), ('        Output: chartevents_8.row_id, chartevents_8.subject_id, chartevents_8.hadm_id, chartevents_8.icustay_id, chartevents_8.itemid, chartevents_8.charttime, chartevents_8.storetime, chartevents_8.cgid, chartevents_8.value, chartevents_8.valuenum, chartevents_8.valueuom, chartevents_8.warning, chartevents_8.error, chartevents_8.resultstatus, chartevents_8.stopped',), ("        Filter: ((chartevents_8.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_8.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_8.icustay_id = 231756))",), ('        Rows Removed by Filter: 21889027',), ('        Buffers: shared hit=415 read=278428',), ('  ->  Seq Scan on mimiciii.chartevents_9  (cost=0.00..612228.71 rows=1 width=194) (actual time=31102.222..31102.223 rows=0 loops=1)',), ('        Output: chartevents_9.row_id, chartevents_9.subject_id, chartevents_9.hadm_id, chartevents_9.icustay_id, chartevents_9.itemid, chartevents_9.charttime, chartevents_9.storetime, chartevents_9.cgid, chartevents_9.value, chartevents_9.valuenum, chartevents_9.valueuom, chartevents_9.warning, chartevents_9.error, chartevents_9.resultstatus, chartevents_9.stopped',), ("        Filter: ((chartevents_9.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_9.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_9.icustay_id = 231756))",), ('        Rows Removed by Filter: 20000213',), ('        Buffers: shared hit=401 read=261824',), ('  ->  Seq Scan on mimiciii.chartevents_10  (cost=0.00..598603.03 rows=1 width=79) (actual time=15520.774..29453.887 rows=105 loops=1)',), ('        Output: chartevents_10.row_id, chartevents_10.subject_id, chartevents_10.hadm_id, chartevents_10.icustay_id, chartevents_10.itemid, chartevents_10.charttime, chartevents_10.storetime, chartevents_10.cgid, chartevents_10.value, chartevents_10.valuenum, chartevents_10.valueuom, chartevents_10.warning, chartevents_10.error, chartevents_10.resultstatus, chartevents_10.stopped',), ("        Filter: ((chartevents_10.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_10.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_10.icustay_id = 231756))",), ('        Rows Removed by Filter: 19590925',), ('        Buffers: shared hit=397 read=255363',), ('  ->  Seq Scan on mimiciii.chartevents_11  (cost=0.00..554558.96 rows=1 width=192) (actual time=25877.911..26244.331 rows=278 loops=1)',), ('        Output: chartevents_11.row_id, chartevents_11.subject_id, chartevents_11.hadm_id, chartevents_11.icustay_id, chartevents_11.itemid, chartevents_11.charttime, chartevents_11.storetime, chartevents_11.cgid, chartevents_11.value, chartevents_11.valuenum, chartevents_11.valueuom, chartevents_11.warning, chartevents_11.error, chartevents_11.resultstatus, chartevents_11.stopped',), ("        Filter: ((chartevents_11.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_11.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_11.icustay_id = 231756))",), ('        Rows Removed by Filter: 18163948',), ('        Buffers: shared hit=407 read=236278',), ('  ->  Seq Scan on mimiciii.chartevents_12  (cost=0.00..633473.09 rows=1 width=192) (actual time=34721.677..34721.677 rows=0 loops=1)',), ('        Output: chartevents_12.row_id, chartevents_12.subject_id, chartevents_12.hadm_id, chartevents_12.icustay_id, chartevents_12.itemid, chartevents_12.charttime, chartevents_12.storetime, chartevents_12.cgid, chartevents_12.value, chartevents_12.valuenum, chartevents_12.valueuom, chartevents_12.warning, chartevents_12.error, chartevents_12.resultstatus, chartevents_12.stopped',), ("        Filter: ((chartevents_12.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_12.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_12.icustay_id = 231756))",), ('        Rows Removed by Filter: 20501149',), ('        Buffers: shared hit=393 read=274310',), ('  ->  Seq Scan on mimiciii.chartevents_13  (cost=0.00..661272.52 rows=1 width=302) (actual time=35982.499..35982.499 rows=0 loops=1)',), ('        Output: chartevents_13.row_id, chartevents_13.subject_id, chartevents_13.hadm_id, chartevents_13.icustay_id, chartevents_13.itemid, chartevents_13.charttime, chartevents_13.storetime, chartevents_13.cgid, chartevents_13.value, chartevents_13.valuenum, chartevents_13.valueuom, chartevents_13.warning, chartevents_13.error, chartevents_13.resultstatus, chartevents_13.stopped',), ("        Filter: ((chartevents_13.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_13.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_13.icustay_id = 231756))",), ('        Rows Removed by Filter: 21704145',), ('        Buffers: shared hit=393 read=281057',), ('  ->  Seq Scan on mimiciii.chartevents_14  (cost=0.00..587567.31 rows=1 width=305) (actual time=31700.356..31700.356 rows=0 loops=1)',), ('        Output: chartevents_14.row_id, chartevents_14.subject_id, chartevents_14.hadm_id, chartevents_14.icustay_id, chartevents_14.itemid, chartevents_14.charttime, chartevents_14.storetime, chartevents_14.cgid, chartevents_14.value, chartevents_14.valuenum, chartevents_14.valueuom, chartevents_14.warning, chartevents_14.error, chartevents_14.resultstatus, chartevents_14.stopped',), ("        Filter: ((chartevents_14.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_14.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_14.icustay_id = 231756))",), ('        Rows Removed by Filter: 19407331',), ('        Buffers: shared hit=399 read=247540',), ('  ->  Seq Scan on mimiciii.chartevents_15  (cost=0.00..617572.09 rows=1 width=304) (actual time=25923.270..25923.270 rows=0 loops=1)',), ('        Output: chartevents_15.row_id, chartevents_15.subject_id, chartevents_15.hadm_id, chartevents_15.icustay_id, chartevents_15.itemid, chartevents_15.charttime, chartevents_15.storetime, chartevents_15.cgid, chartevents_15.value, chartevents_15.valuenum, chartevents_15.valueuom, chartevents_15.warning, chartevents_15.error, chartevents_15.resultstatus, chartevents_15.stopped',), ("        Filter: ((chartevents_15.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_15.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_15.icustay_id = 231756))",), ('        Rows Removed by Filter: 20452747',), ('        Buffers: shared hit=60769 read=198880',), ('  ->  Seq Scan on mimiciii.chartevents_16  (cost=0.00..608704.94 rows=1 width=302) (actual time=25604.227..25604.227 rows=0 loops=1)',), ('        Output: chartevents_16.row_id, chartevents_16.subject_id, chartevents_16.hadm_id, chartevents_16.icustay_id, chartevents_16.itemid, chartevents_16.charttime, chartevents_16.storetime, chartevents_16.cgid, chartevents_16.value, chartevents_16.valuenum, chartevents_16.valueuom, chartevents_16.warning, chartevents_16.error, chartevents_16.resultstatus, chartevents_16.stopped',), ("        Filter: ((chartevents_16.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_16.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_16.icustay_id = 231756))",), ('        Rows Removed by Filter: 20194567',), ('        Buffers: shared hit=52645 read=202655',), ('  ->  Seq Scan on mimiciii.chartevents_17  (cost=0.00..328523.65 rows=1 width=311) (actual time=14147.965..14147.965 rows=0 loops=1)',), ('        Output: chartevents_17.row_id, chartevents_17.subject_id, chartevents_17.hadm_id, chartevents_17.icustay_id, chartevents_17.itemid, chartevents_17.charttime, chartevents_17.storetime, chartevents_17.cgid, chartevents_17.value, chartevents_17.valuenum, chartevents_17.valueuom, chartevents_17.warning, chartevents_17.error, chartevents_17.resultstatus, chartevents_17.stopped',), ("        Filter: ((chartevents_17.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_17.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_17.icustay_id = 231756))",), ('        Rows Removed by Filter: 10721694',), ('        Buffers: shared hit=362 read=140532',), ('Planning time: 0.832 ms',), ('Execution time: 699632.758 ms',)]
Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE): https://pastebin.com/h8YXptck

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE):

[('Append  (cost=0.00..10081481.47 rows=18 width=241) (actual time=0.076..699631.307 rows=6588 loops=1)',), ('  Buffers: shared hit=119402 read=4174611',), ('  ->  Seq Scan on mimiciii.chartevents  (cost=0.00..0.00 rows=1 width=926) (actual time=0.002..0.002 rows=0 loops=1)',), ('        Output: chartevents.row_id, chartevents.subject_id, chartevents.hadm_id, chartevents.icustay_id, chartevents.itemid, chartevents.charttime, chartevents.storetime, chartevents.cgid, chartevents.value, chartevents.valuenum, chartevents.valueuom, chartevents.warning, chartevents.error, chartevents.resultstatus, chartevents.stopped',), ("        Filter: ((chartevents.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents.icustay_id = 231756))",), ('  ->  Seq Scan on mimiciii.chartevents_1  (cost=0.00..560697.60 rows=1 width=83) (actual time=0.073..63067.428 rows=524 loops=1)',), ('        Output: chartevents_1.row_id, chartevents_1.subject_id, chartevents_1.hadm_id, chartevents_1.icustay_id, chartevents_1.itemid, chartevents_1.charttime, chartevents_1.storetime, chartevents_1.cgid, chartevents_1.value, chartevents_1.valuenum, chartevents_1.valueuom, chartevents_1.warning, chartevents_1.error, chartevents_1.resultstatus, chartevents_1.stopped',), ("        Filter: ((chartevents_1.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_1.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_1.icustay_id = 231756))",), ('        Rows Removed by Filter: 18385796',), ('        Buffers: shared hit=484 read=238453',), ('  ->  Seq Scan on mimiciii.chartevents_2  (cost=0.00..594339.70 rows=1 width=196) (actual time=57788.561..66593.194 rows=1043 loops=1)',), ('        Output: chartevents_2.row_id, chartevents_2.subject_id, chartevents_2.hadm_id, chartevents_2.icustay_id, chartevents_2.itemid, chartevents_2.charttime, chartevents_2.storetime, chartevents_2.cgid, chartevents_2.value, chartevents_2.valuenum, chartevents_2.valueuom, chartevents_2.warning, chartevents_2.error, chartevents_2.resultstatus, chartevents_2.stopped',), ("        Filter: ((chartevents_2.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_2.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_2.icustay_id = 231756))",), ('        Rows Removed by Filter: 19646198',), ('        Buffers: shared hit=329 read=250184',), ('  ->  Seq Scan on mimiciii.chartevents_3  (cost=0.00..637061.66 rows=1 width=194) (actual time=53381.363..74843.690 rows=548 loops=1)',), ('        Output: chartevents_3.row_id, chartevents_3.subject_id, chartevents_3.hadm_id, chartevents_3.icustay_id, chartevents_3.itemid, chartevents_3.charttime, chartevents_3.storetime, chartevents_3.cgid, chartevents_3.value, chartevents_3.valuenum, chartevents_3.valueuom, chartevents_3.warning, chartevents_3.error, chartevents_3.resultstatus, chartevents_3.stopped',), ("        Filter: ((chartevents_3.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_3.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_3.icustay_id = 231756))",), ('        Rows Removed by Filter: 20927604',), ('        Buffers: shared hit=406 read=270413',), ('  ->  Seq Scan on mimiciii.chartevents_4  (cost=0.00..587540.59 rows=1 width=195) (actual time=46562.302..59885.102 rows=1047 loops=1)',), ('        Output: chartevents_4.row_id, chartevents_4.subject_id, chartevents_4.hadm_id, chartevents_4.icustay_id, chartevents_4.itemid, chartevents_4.charttime, chartevents_4.storetime, chartevents_4.cgid, chartevents_4.value, chartevents_4.valuenum, chartevents_4.valueuom, chartevents_4.warning, chartevents_4.error, chartevents_4.resultstatus, chartevents_4.stopped',), ("        Filter: ((chartevents_4.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_4.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_4.icustay_id = 231756))",), ('        Rows Removed by Filter: 19128587',), ('        Buffers: shared hit=405 read=252367',), ('  ->  Seq Scan on mimiciii.chartevents_5  (cost=0.00..661280.41 rows=1 width=193) (actual time=55998.470..74354.293 rows=1041 loops=1)',), ('        Output: chartevents_5.row_id, chartevents_5.subject_id, chartevents_5.hadm_id, chartevents_5.icustay_id, chartevents_5.itemid, chartevents_5.charttime, chartevents_5.storetime, chartevents_5.cgid, chartevents_5.value, chartevents_5.valuenum, chartevents_5.valueuom, chartevents_5.warning, chartevents_5.error, chartevents_5.resultstatus, chartevents_5.stopped',), ("        Filter: ((chartevents_5.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_5.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_5.icustay_id = 231756))",), ('        Rows Removed by Filter: 21678411',), ('        Buffers: shared hit=396 read=281494',), ('  ->  Seq Scan on mimiciii.chartevents_6  (cost=0.00..541249.00 rows=1 width=195) (actual time=34122.552..37744.519 rows=698 loops=1)',), ('        Output: chartevents_6.row_id, chartevents_6.subject_id, chartevents_6.hadm_id, chartevents_6.icustay_id, chartevents_6.itemid, chartevents_6.charttime, chartevents_6.storetime, chartevents_6.cgid, chartevents_6.value, chartevents_6.valuenum, chartevents_6.valueuom, chartevents_6.warning, chartevents_6.error, chartevents_6.resultstatus, chartevents_6.stopped',), ("        Filter: ((chartevents_6.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_6.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_6.icustay_id = 231756))",), ('        Rows Removed by Filter: 17611302',), ('        Buffers: shared hit=398 read=232641',), ('  ->  Seq Scan on mimiciii.chartevents_7  (cost=0.00..634900.51 rows=1 width=78) (actual time=29254.283..33246.252 rows=920 loops=1)',), ('        Output: chartevents_7.row_id, chartevents_7.subject_id, chartevents_7.hadm_id, chartevents_7.icustay_id, chartevents_7.itemid, chartevents_7.charttime, chartevents_7.storetime, chartevents_7.cgid, chartevents_7.value, chartevents_7.valuenum, chartevents_7.valueuom, chartevents_7.warning, chartevents_7.error, chartevents_7.resultstatus, chartevents_7.stopped',), ("        Filter: ((chartevents_7.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_7.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_7.icustay_id = 231756))",), ('        Rows Removed by Filter: 20702251',), ('        Buffers: shared hit=403 read=272192',), ('  ->  Seq Scan on mimiciii.chartevents_8  (cost=0.00..661907.71 rows=1 width=79) (actual time=31421.236..35013.673 rows=384 loops=1)',), ('        Output: chartevents_8.row_id, chartevents_8.subject_id, chartevents_8.hadm_id, chartevents_8.icustay_id, chartevents_8.itemid, chartevents_8.charttime, chartevents_8.storetime, chartevents_8.cgid, chartevents_8.value, chartevents_8.valuenum, chartevents_8.valueuom, chartevents_8.warning, chartevents_8.error, chartevents_8.resultstatus, chartevents_8.stopped',), ("        Filter: ((chartevents_8.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_8.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_8.icustay_id = 231756))",), ('        Rows Removed by Filter: 21889027',), ('        Buffers: shared hit=415 read=278428',), ('  ->  Seq Scan on mimiciii.chartevents_9  (cost=0.00..612228.71 rows=1 width=194) (actual time=31102.222..31102.223 rows=0 loops=1)',), ('        Output: chartevents_9.row_id, chartevents_9.subject_id, chartevents_9.hadm_id, chartevents_9.icustay_id, chartevents_9.itemid, chartevents_9.charttime, chartevents_9.storetime, chartevents_9.cgid, chartevents_9.value, chartevents_9.valuenum, chartevents_9.valueuom, chartevents_9.warning, chartevents_9.error, chartevents_9.resultstatus, chartevents_9.stopped',), ("        Filter: ((chartevents_9.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_9.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_9.icustay_id = 231756))",), ('        Rows Removed by Filter: 20000213',), ('        Buffers: shared hit=401 read=261824',), ('  ->  Seq Scan on mimiciii.chartevents_10  (cost=0.00..598603.03 rows=1 width=79) (actual time=15520.774..29453.887 rows=105 loops=1)',), ('        Output: chartevents_10.row_id, chartevents_10.subject_id, chartevents_10.hadm_id, chartevents_10.icustay_id, chartevents_10.itemid, chartevents_10.charttime, chartevents_10.storetime, chartevents_10.cgid, chartevents_10.value, chartevents_10.valuenum, chartevents_10.valueuom, chartevents_10.warning, chartevents_10.error, chartevents_10.resultstatus, chartevents_10.stopped',), ("        Filter: ((chartevents_10.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_10.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_10.icustay_id = 231756))",), ('        Rows Removed by Filter: 19590925',), ('        Buffers: shared hit=397 read=255363',), ('  ->  Seq Scan on mimiciii.chartevents_11  (cost=0.00..554558.96 rows=1 width=192) (actual time=25877.911..26244.331 rows=278 loops=1)',), ('        Output: chartevents_11.row_id, chartevents_11.subject_id, chartevents_11.hadm_id, chartevents_11.icustay_id, chartevents_11.itemid, chartevents_11.charttime, chartevents_11.storetime, chartevents_11.cgid, chartevents_11.value, chartevents_11.valuenum, chartevents_11.valueuom, chartevents_11.warning, chartevents_11.error, chartevents_11.resultstatus, chartevents_11.stopped',), ("        Filter: ((chartevents_11.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_11.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_11.icustay_id = 231756))",), ('        Rows Removed by Filter: 18163948',), ('        Buffers: shared hit=407 read=236278',), ('  ->  Seq Scan on mimiciii.chartevents_12  (cost=0.00..633473.09 rows=1 width=192) (actual time=34721.677..34721.677 rows=0 loops=1)',), ('        Output: chartevents_12.row_id, chartevents_12.subject_id, chartevents_12.hadm_id, chartevents_12.icustay_id, chartevents_12.itemid, chartevents_12.charttime, chartevents_12.storetime, chartevents_12.cgid, chartevents_12.value, chartevents_12.valuenum, chartevents_12.valueuom, chartevents_12.warning, chartevents_12.error, chartevents_12.resultstatus, chartevents_12.stopped',), ("        Filter: ((chartevents_12.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_12.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_12.icustay_id = 231756))",), ('        Rows Removed by Filter: 20501149',), ('        Buffers: shared hit=393 read=274310',), ('  ->  Seq Scan on mimiciii.chartevents_13  (cost=0.00..661272.52 rows=1 width=302) (actual time=35982.499..35982.499 rows=0 loops=1)',), ('        Output: chartevents_13.row_id, chartevents_13.subject_id, chartevents_13.hadm_id, chartevents_13.icustay_id, chartevents_13.itemid, chartevents_13.charttime, chartevents_13.storetime, chartevents_13.cgid, chartevents_13.value, chartevents_13.valuenum, chartevents_13.valueuom, chartevents_13.warning, chartevents_13.error, chartevents_13.resultstatus, chartevents_13.stopped',), ("        Filter: ((chartevents_13.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_13.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_13.icustay_id = 231756))",), ('        Rows Removed by Filter: 21704145',), ('        Buffers: shared hit=393 read=281057',), ('  ->  Seq Scan on mimiciii.chartevents_14  (cost=0.00..587567.31 rows=1 width=305) (actual time=31700.356..31700.356 rows=0 loops=1)',), ('        Output: chartevents_14.row_id, chartevents_14.subject_id, chartevents_14.hadm_id, chartevents_14.icustay_id, chartevents_14.itemid, chartevents_14.charttime, chartevents_14.storetime, chartevents_14.cgid, chartevents_14.value, chartevents_14.valuenum, chartevents_14.valueuom, chartevents_14.warning, chartevents_14.error, chartevents_14.resultstatus, chartevents_14.stopped',), ("        Filter: ((chartevents_14.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_14.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_14.icustay_id = 231756))",), ('        Rows Removed by Filter: 19407331',), ('        Buffers: shared hit=399 read=247540',), ('  ->  Seq Scan on mimiciii.chartevents_15  (cost=0.00..617572.09 rows=1 width=304) (actual time=25923.270..25923.270 rows=0 loops=1)',), ('        Output: chartevents_15.row_id, chartevents_15.subject_id, chartevents_15.hadm_id, chartevents_15.icustay_id, chartevents_15.itemid, chartevents_15.charttime, chartevents_15.storetime, chartevents_15.cgid, chartevents_15.value, chartevents_15.valuenum, chartevents_15.valueuom, chartevents_15.warning, chartevents_15.error, chartevents_15.resultstatus, chartevents_15.stopped',), ("        Filter: ((chartevents_15.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_15.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_15.icustay_id = 231756))",), ('        Rows Removed by Filter: 20452747',), ('        Buffers: shared hit=60769 read=198880',), ('  ->  Seq Scan on mimiciii.chartevents_16  (cost=0.00..608704.94 rows=1 width=302) (actual time=25604.227..25604.227 rows=0 loops=1)',), ('        Output: chartevents_16.row_id, chartevents_16.subject_id, chartevents_16.hadm_id, chartevents_16.icustay_id, chartevents_16.itemid, chartevents_16.charttime, chartevents_16.storetime, chartevents_16.cgid, chartevents_16.value, chartevents_16.valuenum, chartevents_16.valueuom, chartevents_16.warning, chartevents_16.error, chartevents_16.resultstatus, chartevents_16.stopped',), ("        Filter: ((chartevents_16.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_16.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_16.icustay_id = 231756))",), ('        Rows Removed by Filter: 20194567',), ('        Buffers: shared hit=52645 read=202655',), ('  ->  Seq Scan on mimiciii.chartevents_17  (cost=0.00..328523.65 rows=1 width=311) (actual time=14147.965..14147.965 rows=0 loops=1)',), ('        Output: chartevents_17.row_id, chartevents_17.subject_id, chartevents_17.hadm_id, chartevents_17.icustay_id, chartevents_17.itemid, chartevents_17.charttime, chartevents_17.storetime, chartevents_17.cgid, chartevents_17.value, chartevents_17.valuenum, chartevents_17.valueuom, chartevents_17.warning, chartevents_17.error, chartevents_17.resultstatus, chartevents_17.stopped',), ("        Filter: ((chartevents_17.charttime >= '2125-11-11 21:10:00'::timestamp without time zone) AND (chartevents_17.charttime <= '2125-11-20 21:49:43'::timestamp without time zone) AND (chartevents_17.icustay_id = 231756))",), ('        Rows Removed by Filter: 10721694',), ('        Buffers: shared hit=362 read=140532',), ('Planning time: 0.832 ms',), ('Execution time: 699632.758 ms',)]

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE): https://pastebin.com/h8YXptck

added EXPLAIN output
Source Link
Ray A.
Ray A.
  • 101
  • 2
edited tags
Link
user1822
user1822
added 10 characters in body
Source Link
user1822
user1822
Source Link
Ray A.
Ray A.
  • 101
  • 2