Не вычислять зачение set=... для update с случае, если этого делать не надо по условию / PostgreSQL

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Не вычислять зачение set=... для update с случае, если этого делать не надо по условию

4 сообщений из 4, страница 1 из 1

Не вычислять зачение set=... для update с случае, если этого делать не надо по условию

#39987971

flashgun

Гость

Коллеги!

Для каждого пользователя надо заполнить поле "впервые появился" в случае, если оно не заполнено. Пишу:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

update 
   users 
set 
   firstpres=(
        select 
             to_timestamp(min(utime)) 
        from 
             hyper 
        where 
             xuid=users.xuid) 
where 
   firstpres is null 
   and users.xuid=2535473458706799;

В таблице hyper около 280 млн строк (timescaledb), соответственно, не очень хочется, чтобы поиск самого раннего значения производился в том случае, если поле firstpres уже заполнено.

Однако, при выполнении запроса я вижу, что
UPDATE 0 (то есть, в базу записи не было, всё правильно)
и EXPLAIN ANALYZE показывает, что поиск по таблице hyper всё равно производится, несмотря на то, что условие where не выполняется.

Можно ли как-то избежать этого, изменив запрос?

...

Рейтинг:

0 / 0

09.08.2020, 20:31

| Ответить | Цитировать | Написать

Не вычислять зачение set=... для update с случае, если этого делать не надо по условию

#39987980

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

flashgun,

Вы бы результаты explain analyze бы показали для начала...

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

09.08.2020, 21:13

| Ответить | Цитировать | Написать

Не вычислять зачение set=... для update с случае, если этого делать не надо по условию

#39987999

flashgun

Гость

Maxim Boguk,

Ох, похоже вы правы:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.

$ echo 'explain (analyze,buffers) update users set firstpres=(select to_timestamp(min(utime)) from hyper where xuid=users.xuid) where firstpres is null and users.xuid=2535473458706799' | psql xbox
Timing is on.
                                                                                               QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on users  (cost=0.42..6.54 rows=1 width=1436) (actual time=0.012..0.013 rows=0 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using users_pkey on users  (cost=0.42..6.54 rows=1 width=1436) (actual time=0.012..0.012 rows=0 loops=1)
         Index Cond: (xuid = '2535473458706799'::bigint)
         Filter: (firstpres IS NULL)
         Rows Removed by Filter: 1
         Buffers: shared hit=4
         SubPlan 2
           ->  Result  (cost=3.88..3.90 rows=1 width=8) (never executed)
                 InitPlan 1 (returns $1)
                   ->  Limit  (cost=0.43..3.88 rows=1 width=4) (never executed)
                         ->  Custom Scan (ChunkAppend) on hyper  (cost=0.43..40581.20 rows=119 width=4) (never executed)
                               Order: hyper.utime
                               ->  Index Only Scan using _hyper_1_83_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_83_chunk  (cost=0.43..40581.20 rows=119 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_84_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_84_chunk  (cost=0.43..43015.07 rows=125 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_85_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_85_chunk  (cost=0.43..45655.12 rows=129 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_82_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_82_chunk  (cost=0.43..49771.59 rows=145 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_80_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_80_chunk  (cost=0.43..59637.58 rows=134 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_78_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_78_chunk  (cost=0.43..56011.67 rows=139 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_79_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_79_chunk  (cost=0.43..55125.09 rows=138 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_81_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_81_chunk  (cost=0.43..46157.80 rows=134 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_74_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_74_chunk  (cost=0.43..54792.21 rows=135 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_75_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_75_chunk  (cost=0.43..54685.95 rows=139 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_76_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_76_chunk  (cost=0.43..51263.50 rows=133 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_77_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_77_chunk  (cost=0.43..55919.53 rows=138 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_70_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_70_chunk  (cost=0.43..54869.62 rows=139 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_71_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_71_chunk  (cost=0.43..56467.06 rows=142 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_72_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_72_chunk  (cost=0.43..50094.50 rows=124 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_73_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_73_chunk  (cost=0.43..53656.45 rows=133 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_66_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_66_chunk  (cost=0.43..27850.65 rows=74 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_68_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_68_chunk  (cost=0.43..52110.83 rows=132 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_69_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_69_chunk  (cost=0.43..53525.25 rows=134 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_67_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_67_chunk  (cost=0.43..52808.30 rows=135 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_61_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_61_chunk  (cost=0.43..53321.67 rows=133 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_64_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_64_chunk  (cost=0.43..54419.03 rows=137 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_65_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_65_chunk  (cost=0.43..51993.57 rows=131 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_62_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_62_chunk  (cost=0.43..43131.63 rows=114 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_63_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_63_chunk  (cost=0.43..53970.01 rows=137 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_57_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_57_chunk  (cost=0.43..51490.20 rows=131 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_58_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_58_chunk  (cost=0.43..52385.92 rows=133 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_59_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_59_chunk  (cost=0.42..19945.94 rows=63 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_60_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_60_chunk  (cost=0.43..47653.98 rows=126 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_52_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_52_chunk  (cost=0.43..56289.87 rows=144 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_54_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_54_chunk  (cost=0.43..40215.35 rows=103 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_56_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_56_chunk  (cost=0.43..52158.05 rows=130 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_55_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_55_chunk  (cost=0.43..56374.37 rows=143 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_53_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_53_chunk  (cost=0.43..56475.74 rows=141 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_48_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_48_chunk  (cost=0.43..58986.10 rows=146 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_51_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_51_chunk  (cost=0.43..57586.13 rows=141 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_49_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_49_chunk  (cost=0.43..38261.68 rows=96 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_50_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_50_chunk  (cost=0.43..54040.19 rows=129 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_44_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_44_chunk  (cost=0.42..11809.73 rows=32 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_47_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_47_chunk  (cost=0.43..44897.08 rows=108 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_45_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_45_chunk  (cost=0.43..54080.72 rows=127 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_46_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_46_chunk  (cost=0.43..57396.17 rows=134 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_40_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_40_chunk  (cost=0.43..32554.53 rows=78 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_39_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_39_chunk  (cost=0.43..30220.41 rows=80 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_42_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_42_chunk  (cost=0.43..52270.89 rows=132 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_43_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_43_chunk  (cost=0.43..53117.73 rows=133 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_41_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_41_chunk  (cost=0.43..39712.13 rows=92 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_35_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_35_chunk  (cost=0.43..62676.09 rows=143 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_37_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_37_chunk  (cost=0.43..65709.40 rows=133 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_38_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_38_chunk  (cost=0.43..73073.56 rows=142 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_36_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_36_chunk  (cost=0.43..68697.25 rows=132 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_30_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_30_chunk  (cost=0.43..75941.69 rows=143 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_32_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_32_chunk  (cost=0.43..76456.76 rows=145 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_31_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_31_chunk  (cost=0.43..78153.69 rows=145 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_33_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_33_chunk  (cost=0.43..81464.38 rows=150 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_34_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_34_chunk  (cost=0.43..66518.96 rows=122 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_28_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_28_chunk  (cost=0.43..21987.97 rows=45 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_29_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_29_chunk  (cost=0.43..77635.03 rows=137 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_24_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_24_chunk  (cost=0.43..78776.94 rows=139 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_25_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_25_chunk  (cost=0.43..80633.64 rows=140 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_26_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_26_chunk  (cost=0.43..81539.66 rows=143 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_27_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_27_chunk  (cost=0.43..82410.35 rows=141 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_20_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_20_chunk  (cost=0.43..78550.20 rows=135 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_21_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_21_chunk  (cost=0.43..84569.17 rows=146 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_22_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_22_chunk  (cost=0.43..92472.50 rows=154 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_23_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_23_chunk  (cost=0.43..105781.65 rows=170 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_16_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_16_chunk  (cost=0.43..116282.86 rows=184 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_15_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_15_chunk  (cost=0.43..122646.02 rows=191 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_17_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_17_chunk  (cost=0.43..116830.94 rows=183 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_18_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_18_chunk  (cost=0.43..113515.03 rows=176 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_19_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_19_chunk  (cost=0.43..112401.42 rows=178 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_11_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_11_chunk  (cost=0.43..94209.95 rows=149 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_12_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_12_chunk  (cost=0.43..112677.25 rows=176 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_13_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_13_chunk  (cost=0.43..110549.29 rows=175 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_14_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_14_chunk  (cost=0.43..106140.61 rows=161 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_8_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_8_chunk  (cost=0.43..104641.35 rows=164 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_7_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_7_chunk  (cost=0.43..100777.18 rows=162 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_9_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_9_chunk  (cost=0.43..102006.16 rows=164 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_10_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_10_chunk  (cost=0.43..99675.76 rows=160 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_2_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_2_chunk  (cost=0.43..97469.17 rows=156 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_3_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_3_chunk  (cost=0.43..94186.95 rows=155 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_4_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_4_chunk  (cost=0.43..92018.39 rows=150 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_5_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_5_chunk  (cost=0.43..92136.29 rows=151 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Only Scan using _hyper_1_6_chunk_hyper_utime_xuid_titleid_devid_actid_secs_idx on _hyper_1_6_chunk  (cost=0.43..92363.62 rows=149 width=4) (never executed)
                                     Index Cond: ((utime IS NOT NULL) AND (xuid = users.xuid))
                                     Heap Fetches: 0
                               ->  Index Scan Backward using _hyper_1_1_chunk_hyper_utime_idx on _hyper_1_1_chunk  (cost=0.43..196132.59 rows=183 width=4) (never executed)
                                     Index Cond: (utime IS NOT NULL)
                                     Filter: (xuid = users.xuid)
                               ->  Index Scan Backward using _hyper_1_86_chunk_hyper_utime_idx on _hyper_1_86_chunk  (cost=0.56..256690.26 rows=155 width=4) (never executed)
                                     Index Cond: (utime IS NOT NULL)
                                     Filter: (xuid = users.xuid)
 Planning Time: 33.883 ms
 Execution Time: 0.849 ms
(273 rows)

Time: 46.765 ms
$

Я правильно понимаю, что "(never executed)" для каждого узла как раз и означает, что планирование для этого запроса производилось, стоимость оценивалась, но вычислений, как таковых не было?

Просто увидев такой вывод у explain, я почему-то решил, что все узлы выполнялись...

...

Рейтинг:

0 / 0

09.08.2020, 22:51

| Ответить | Цитировать | Написать

Не вычислять зачение set=... для update с случае, если этого делать не надо по условию

#39988074

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

flashgun

"(never executed)" для каждого узла как раз и означает, что планирование для этого запроса производилось, стоимость оценивалась, но вычислений, как таковых не было?

Да, в текстовом формате так выводятся узлы с loops=0, то есть executor их не выполнял.

Планировщик же не знает, увидит ли executor хоть одну строку для update, а потому должен предоставить план на случай если да, найдётся такая строка.

...

Рейтинг:

0 / 0

10.08.2020, 10:37

| Ответить | Цитировать | Написать

4 сообщений из 4, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&fpage=24&tid=1994535]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	38ms
get topic data:	12ms
get forum data:	3ms
get page messages:	39ms
get tp. blocked users:	2ms
others:	14ms

total:	139ms