I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is growing much faster than expected.
The query is something like:
SELECT * FROM some_view_name
WHERE (1 = 1)
AND field1 = 'whatever'
AND field2 >= 'YYYYMMDDHHMMSS_TEXT' ORDER BY field3, field2 ASC;
Related view is a complex join of tables and more views. And it runs quite fast when field2, that's a text date field, is something near to current date. But as date is increased (in the past) it slows, and slows, and… for a query about current_date - 1 year it can take almost 20 minutes to complete, returning 2,234,407 rows. Complete view has 17,475,533 rows.
I'm working on analyzing the mean of the inner view code because I'm new at this environment and still don't know functionality. While I discover how to reformulate the view/query I've come over Tatiyants' Postgres EXPLAIN Visualizer (pev) and I've read a ton of posts in blogs, StackOverflow and here about Postgres performance. But I'm still not sure how to begin tunning my queries. The first thing that's annoying me is the badge bad estimate. What should I do with this? I have several nodes with this badge, like Bitmap Index/Heap Scan (both), Nested Loop, Sort, Merge… In one of the nodes, execution plan said "underestimate rows by 124x". I've run ANALYZE in the DB, rerun execution plan and when reloading it in PEV now it says "underestimate rows by 132x". So what should I check/evaluate in order to make this badge disappear?
As PEV plans are stored locally, I'm posting a screenshot of it:
select version();
> PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC)
> 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
Related to DB config, I guess config parameters have been left by default after installation, but can check them if there is any parameter relevant to this situation. I don't have access to production server so I can't post hw config, but can ask if needed.
I'm aware of materialized views and the performance improvement they provide, and I know there are solutions to implement them in PostgreSQL versions prior to 9.3 (where they were introduced), but by now this is not a solution to take in consideration. Also upgrade Postgres version is not a solution by now.
Any suggestion will be really appreciated.
EDIT:
Explain plan in text:
Sort (cost=378583.87..378620.19 rows=14528 width=178) (actual time=213051.194..214405.039 rows=3015686 loops=1)
Output: pos_boyas.id_traza_boya, public.usuarios_controllers_tbl.usuario_controller, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, pos_boyas.num_boya, pos_boyas.latitud_boya, pos_boyas.longitud_boya, pos_boyas.estado_tracking, pos_boyas.estado_flash, pos_boyas.estado_sos, pos_boyas.estado_ack, pos_boyas.estado_gps, pos_boyas.bateria, pos_boyas.fecha_posicion, pos_boyas.velocidad, pos_boyas.rumbo, barcos.id_barco, barcos.nombre_barco, (COALESCE((CASE WHEN (public.usuarios_controllers_tbl.id_usuario_controller IS NOT NULL) THEN 1 ELSE 0 END), 0)), (COALESCE(estados_notify.zona_prohibida, 0)), (COALESCE(estados_notify.velocidad_lenta, 0)), (COALESCE(estados_notify.sin_transmision, 0)), imeis_isn_tbl.alias
Sort Key: imeis_isn_tbl.id_isn_imei, pos_boyas.fecha_posicion
Sort Method: external merge Disk: 588440kB
Buffers: shared hit=9793574 read=5194007 dirtied=11 written=983, temp read=408320 written=408306
-> Nested Loop Left Join (cost=78087.84..376288.01 rows=14528 width=178) (actual time=38788.260..177034.008 rows=3015686 loops=1)
Output: pos_boyas.id_traza_boya, public.usuarios_controllers_tbl.usuario_controller, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, pos_boyas.num_boya, pos_boyas.latitud_boya, pos_boyas.longitud_boya, pos_boyas.estado_tracking, pos_boyas.estado_flash, pos_boyas.estado_sos, pos_boyas.estado_ack, pos_boyas.estado_gps, pos_boyas.bateria, pos_boyas.fecha_posicion, pos_boyas.velocidad, pos_boyas.rumbo, barcos.id_barco, barcos.nombre_barco, COALESCE((CASE WHEN (public.usuarios_controllers_tbl.id_usuario_controller IS NOT NULL) THEN 1 ELSE 0 END), 0), COALESCE(estados_notify.zona_prohibida, 0), COALESCE(estados_notify.velocidad_lenta, 0), COALESCE(estados_notify.sin_transmision, 0), imeis_isn_tbl.alias
Buffers: shared hit=9793574 read=5194007 dirtied=11 written=983, temp read=73952 written=73938
-> Hash Right Join (cost=78087.84..251384.24 rows=14528 width=166) (actual time=38788.197..130480.296 rows=3015686 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, pos_boyas.id_traza_boya, pos_boyas.num_boya, pos_boyas.latitud_boya, pos_boyas.longitud_boya, pos_boyas.estado_tracking, pos_boyas.estado_flash, pos_boyas.estado_sos, pos_boyas.estado_ack, pos_boyas.estado_gps, pos_boyas.bateria, pos_boyas.fecha_posicion, pos_boyas.velocidad, pos_boyas.rumbo, barcos.id_barco, barcos.nombre_barco, (CASE WHEN (public.usuarios_controllers_tbl.id_usuario_controller IS NOT NULL) THEN 1 ELSE 0 END)
Hash Cond: ((barcos.id_barco = barcos.id_barco) AND (usuarios.id_usuario_controller = public.usuarios_controllers_tbl.id_usuario_controller))
Buffers: shared hit=221373 read=2784664 dirtied=5 written=186, temp read=73952 written=73938
-> Hash Left Join (cost=957.57..91273.92 rows=2771388 width=12) (actual time=17.736..4808.980 rows=2771388 loops=1)
Output: barcos.id_barco, usuarios.id_usuario_controller, CASE WHEN (public.usuarios_controllers_tbl.id_usuario_controller IS NOT NULL) THEN 1 ELSE 0 END
Hash Cond: ((barcos.id_barco = barcos_tbl.id_barco) AND ((usuarios.usuario_controller)::text = (public.usuarios_controllers_tbl.usuario_controller)::text))
Buffers: shared hit=74 read=168
-> Nested Loop (cost=0.00..34768.76 rows=2771388 width=25) (actual time=0.039..1567.517 rows=2771388 loops=1)
Output: barcos.id_barco, usuarios.usuario_controller, usuarios.id_usuario_controller
Buffers: shared hit=60 read=32
-> Index Scan using usuarios_controllers_tbl_pkey on public.usuarios_controllers_tbl usuarios (cost=0.00..75.26 rows=1149 width=21) (actual time=0.025..6.553 rows=1149 loops=1)
Output: usuarios.id_usuario_controller, usuarios.usuario_controller, usuarios.id_usuario_controller_zcc, usuarios.cuenta_demo, usuarios.ver_boyas, usuarios.ver_todos_barcos
Buffers: shared hit=56 read=15
-> Materialize (cost=0.00..57.18 rows=2412 width=4) (actual time=0.000..0.581 rows=2412 loops=1149)
Output: barcos.id_barco
Buffers: shared hit=4 read=17
-> Seq Scan on public.barcos_tbl barcos (cost=0.00..45.12 rows=2412 width=4) (actual time=0.009..1.234 rows=2412 loops=1)
Output: barcos.id_barco
Buffers: shared hit=4 read=17
-> Hash (cost=777.81..777.81 rows=11984 width=25) (actual time=17.670..17.670 rows=11984 loops=1)
Output: barcos_tbl.id_barco, public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller
Buckets: 2048 Batches: 1 Memory Usage: 722kB
Buffers: shared hit=14 read=136
-> Hash Join (cost=248.43..777.81 rows=11984 width=25) (actual time=3.373..14.056 rows=11984 loops=1)
Output: barcos_tbl.id_barco, public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller
Hash Cond: (usuarios_controllers_barcos_tbl.id_barco = barcos_tbl.id_barco)
Buffers: shared hit=14 read=136
-> Hash Join (cost=173.16..522.78 rows=11984 width=25) (actual time=2.428..8.821 rows=11984 loops=1)
Output: public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller, usuarios_controllers_barcos_tbl.id_barco
Hash Cond: (usuarios_controllers_barcos_tbl.id_usuario_controller = public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller)
Buffers: shared hit=5 read=124
-> Seq Scan on public.usuarios_controllers_barcos_tbl (cost=0.00..184.84 rows=11984 width=8) (actual time=0.009..2.028 rows=11984 loops=1)
Output: usuarios_controllers_barcos_tbl.id_usuario_controller_barco, usuarios_controllers_barcos_tbl.id_usuario_controller, usuarios_controllers_barcos_tbl.id_barco
Buffers: shared read=65
-> Hash (cost=158.79..158.79 rows=1149 width=25) (actual time=2.410..2.410 rows=1149 loops=1)
Output: public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller, public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller
Buckets: 1024 Batches: 1 Memory Usage: 67kB
Buffers: shared hit=5 read=59
-> Hash Join (cost=68.71..158.79 rows=1149 width=25) (actual time=0.953..2.172 rows=1149 loops=1)
Output: public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller, public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller
Hash Cond: (public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller = public.usuarios_controllers_tbl.id_usuario_controller)
Buffers: shared hit=5 read=59
-> Hash Join (cost=32.85..107.14 rows=1149 width=4) (actual time=0.448..1.317 rows=1149 loops=1)
Output: public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller
Hash Cond: (public.usuarios_claves_tbl.id_usuario = public.usuarios_claves_usuarios_controllers_tbl.id_usuario)
Buffers: shared hit=4 read=50
-> Seq Scan on public.usuarios_claves_tbl (cost=0.00..58.49 rows=1149 width=4) (actual time=0.008..0.412 rows=1149 loops=1)
Output: public.usuarios_claves_tbl.id_usuario, public.usuarios_claves_tbl.usuario, public.usuarios_claves_tbl.clave, public.usuarios_claves_tbl.salt, public.usuarios_claves_tbl.clave_original, public.usuarios_claves_tbl.clave_anulada, public.usuarios_claves_tbl.estado
Buffers: shared hit=2 read=45
-> Hash (cost=18.49..18.49 rows=1149 width=8) (actual time=0.431..0.431 rows=1149 loops=1)
Output: public.usuarios_claves_usuarios_controllers_tbl.id_usuario, public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller
Buckets: 1024 Batches: 1 Memory Usage: 45kB
Buffers: shared hit=2 read=5
-> Seq Scan on public.usuarios_claves_usuarios_controllers_tbl (cost=0.00..18.49 rows=1149 width=8) (actual time=0.003..0.226 rows=1149 loops=1)
Output: public.usuarios_claves_usuarios_controllers_tbl.id_usuario, public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller
Buffers: shared hit=2 read=5
-> Hash (cost=21.49..21.49 rows=1149 width=21) (actual time=0.499..0.499 rows=1149 loops=1)
Output: public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller
Buckets: 1024 Batches: 1 Memory Usage: 61kB
Buffers: shared hit=1 read=9
-> Seq Scan on public.usuarios_controllers_tbl (cost=0.00..21.49 rows=1149 width=21) (actual time=0.004..0.252 rows=1149 loops=1)
Output: public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller
Buffers: shared hit=1 read=9
-> Hash (cost=45.12..45.12 rows=2412 width=4) (actual time=0.939..0.939 rows=2412 loops=1)
Output: barcos_tbl.id_barco
Buckets: 1024 Batches: 1 Memory Usage: 85kB
Buffers: shared hit=9 read=12
-> Seq Scan on public.barcos_tbl (cost=0.00..45.12 rows=2412 width=4) (actual time=0.002..0.520 rows=2412 loops=1)
Output: barcos_tbl.id_barco
Buffers: shared hit=9 read=12
-> Hash (cost=76571.36..76571.36 rows=14528 width=166) (actual time=38768.035..38768.035 rows=3015686 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, pos_boyas.id_traza_boya, pos_boyas.num_boya, pos_boyas.latitud_boya, pos_boyas.longitud_boya, pos_boyas.estado_tracking, pos_boyas.estado_flash, pos_boyas.estado_sos, pos_boyas.estado_ack, pos_boyas.estado_gps, pos_boyas.bateria, pos_boyas.fecha_posicion, pos_boyas.velocidad, pos_boyas.rumbo, barcos.id_barco, barcos.nombre_barco
Buckets: 1024 Batches: 8 (originally 4) Memory Usage: 152904kB
Buffers: shared hit=221299 read=2784496 dirtied=5 written=186, temp written=64451
-> Nested Loop (cost=364.41..76571.36 rows=14528 width=166) (actual time=513.666..36685.672 rows=3015686 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, pos_boyas.id_traza_boya, pos_boyas.num_boya, pos_boyas.latitud_boya, pos_boyas.longitud_boya, pos_boyas.estado_tracking, pos_boyas.estado_flash, pos_boyas.estado_sos, pos_boyas.estado_ack, pos_boyas.estado_gps, pos_boyas.bateria, pos_boyas.fecha_posicion, pos_boyas.velocidad, pos_boyas.rumbo, barcos.id_barco, barcos.nombre_barco
Buffers: shared hit=221299 read=2784496 dirtied=5 written=186
-> Nested Loop Left Join (cost=308.57..620.66 rows=15 width=87) (actual time=83.267..183.011 rows=1982 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, barcos.id_barco, barcos.nombre_barco
Buffers: shared hit=22216 read=1073
-> Nested Loop (cost=308.57..521.23 rows=15 width=76) (actual time=83.237..148.049 rows=1982 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, comun_imei.id_barco
Buffers: shared hit=17553 read=372
-> Nested Loop (cost=308.57..415.88 rows=15 width=80) (actual time=73.830..120.167 rows=1982 loops=1)
Output: public.usuarios_claves_usuarios_controllers_tbl.id_usuario, public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, comun_imei.id_barco
Join Filter: (public.usuarios_controllers_tbl.id_usuario_controller = public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller)
Buffers: shared hit=11731 read=247
-> Merge Left Join (cost=308.57..318.78 rows=15 width=80) (actual time=73.811..87.641 rows=1982 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias, comun_imei.id_barco
Merge Cond: (imeis_isn_tbl.id_isn_imei = comun_imei.id_isn_imei)
Buffers: shared hit=5911 read=121
-> Sort (cost=157.97..158.01 rows=15 width=76) (actual time=72.856..75.019 rows=1982 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias
Sort Key: usuarios_controllers_imeis_isn_tbl.id_isn_imei
Sort Method: quicksort Memory: 327kB
Buffers: shared hit=5908 read=104
-> Nested Loop (cost=4.37..157.68 rows=15 width=76) (actual time=67.455..72.049 rows=1982 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.alias
Buffers: shared hit=5908 read=104
-> Nested Loop (cost=4.37..52.77 rows=15 width=29) (actual time=67.424..68.430 rows=1982 loops=1)
Output: public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei
Buffers: shared hit=7 read=45
-> Index Scan using usuarios_controllers_tbl_usuario_controller_idx on public.usuarios_controllers_tbl (cost=0.00..8.27 rows=1 width=21) (actual time=0.010..0.012 rows=1 loops=1)
Output: public.usuarios_controllers_tbl.id_usuario_controller, public.usuarios_controllers_tbl.usuario_controller, public.usuarios_controllers_tbl.id_usuario_controller_zcc, public.usuarios_controllers_tbl.cuenta_demo, public.usuarios_controllers_tbl.ver_boyas, public.usuarios_controllers_tbl.ver_todos_barcos
Index Cond: ((public.usuarios_controllers_tbl.usuario_controller)::text = 'whatever'::text)
Buffers: shared hit=3
-> Bitmap Heap Scan on public.usuarios_controllers_imeis_isn_tbl (cost=4.37..44.35 rows=15 width=8) (actual time=67.406..67.944 rows=1982 loops=1)
Output: usuarios_controllers_imeis_isn_tbl.id_usuario_controller_imei, usuarios_controllers_imeis_isn_tbl.id_usuario_controller, usuarios_controllers_imeis_isn_tbl.id_isn_imei
Recheck Cond: (usuarios_controllers_imeis_isn_tbl.id_usuario_controller = public.usuarios_controllers_tbl.id_usuario_controller)
Buffers: shared hit=4 read=45
-> Bitmap Index Scan on usuarios_controllers_imeis_isn_tbl_id_usuario_controller_idx (cost=0.00..4.37 rows=15 width=0) (actual time=67.389..67.389 rows=1982 loops=1)
Index Cond: (usuarios_controllers_imeis_isn_tbl.id_usuario_controller = public.usuarios_controllers_tbl.id_usuario_controller)
Buffers: shared hit=2 read=7
-> Index Scan using imeis_isn_tbl_pkey on public.imeis_isn_tbl (cost=0.00..6.98 rows=1 width=47) (actual time=0.001..0.001 rows=1 loops=1982)
Output: imeis_isn_tbl.id_isn_imei, imeis_isn_tbl.isn, imeis_isn_tbl.imei, imeis_isn_tbl.tipo_comunicador, imeis_isn_tbl.alias, imeis_isn_tbl.boya_sos, imeis_isn_tbl.gestion_mensajes, imeis_isn_tbl.fecha_ult_modificacion, imeis_isn_tbl.id_isn_imei_tmp, imeis_isn_tbl.bluetooth, imeis_isn_tbl.geofence, imeis_isn_tbl.sonda
Index Cond: (imeis_isn_tbl.id_isn_imei = usuarios_controllers_imeis_isn_tbl.id_isn_imei)
Buffers: shared hit=5901 read=59
-> Sort (cost=150.59..155.63 rows=2013 width=8) (actual time=0.920..3.696 rows=2007 loops=1)
Output: comun_imei.id_isn_imei, comun_imei.id_barco
Sort Key: comun_imei.id_isn_imei
Sort Method: quicksort Memory: 143kB
Buffers: shared hit=3 read=17
-> Seq Scan on public.barcos_imeis_tbl comun_imei (cost=0.00..40.13 rows=2013 width=8) (actual time=0.011..0.422 rows=2013 loops=1)
Output: comun_imei.id_isn_imei, comun_imei.id_barco
Buffers: shared hit=3 read=17
-> Index Scan using usuarios_claves_usuarios_controllers__id_usuario_controller_idx on public.usuarios_claves_usuarios_controllers_tbl (cost=0.00..6.46 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=1982)
Output: public.usuarios_claves_usuarios_controllers_tbl.id_usu_clave_usu_controller, public.usuarios_claves_usuarios_controllers_tbl.id_usuario, public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller
Index Cond: (public.usuarios_claves_usuarios_controllers_tbl.id_usuario_controller = usuarios_controllers_imeis_isn_tbl.id_usuario_controller)
Buffers: shared hit=5820 read=126
-> Index Only Scan using usuarios_claves_tbl_pkey on public.usuarios_claves_tbl (cost=0.00..7.01 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1982)
Output: public.usuarios_claves_tbl.id_usuario
Index Cond: (public.usuarios_claves_tbl.id_usuario = public.usuarios_claves_usuarios_controllers_tbl.id_usuario)
Heap Fetches: 1982
Buffers: shared hit=5822 read=125
-> Index Scan using barcos_tbl_pkey on public.barcos_tbl barcos (cost=0.00..6.62 rows=1 width=15) (actual time=0.013..0.015 rows=1 loops=1982)
Output: barcos.id_barco, barcos.nombre_barco
Index Cond: (comun_imei.id_barco = barcos.id_barco)
Buffers: shared hit=4663 read=701
-> Bitmap Heap Scan on public.traza_boyas_tbl pos_boyas (cost=55.85..5047.66 rows=1572 width=87) (actual time=6.041..17.728 rows=1522 loops=1982)
Output: pos_boyas.id_traza_boya, pos_boyas.num_boya, pos_boyas.isn, pos_boyas.id_barco, pos_boyas.tipo_boya, pos_boyas.cabecera, pos_boyas.latitud_boya, pos_boyas.longitud_boya, pos_boyas.velocidad, pos_boyas.rumbo, pos_boyas.estado_tracking, pos_boyas.estado_flash, pos_boyas.estado_sos, pos_boyas.estado_ack, pos_boyas.estado_gps, pos_boyas.bateria, pos_boyas.en_agua, pos_boyas.imei, pos_boyas.fecha_posicion, pos_boyas.minutos_ventana_on, pos_boyas.minutos_ventana_off, pos_boyas.alarma_geofence, pos_boyas.estado_geofence, pos_boyas.primer_arranque, pos_boyas.id_isn_imei
Recheck Cond: ((pos_boyas.id_isn_imei = usuarios_controllers_imeis_isn_tbl.id_isn_imei) AND (pos_boyas.fecha_posicion >= '20180731140755'::bpchar))
Rows Removed by Index Recheck: 535
Buffers: shared hit=199083 read=2783423 dirtied=5 written=186
-> Bitmap Index Scan on traza_boyas_tbl_id_isn_imei_fecha_idx (cost=0.00..55.45 rows=1572 width=0) (actual time=5.788..5.788 rows=1522 loops=1982)
Index Cond: ((pos_boyas.id_isn_imei = usuarios_controllers_imeis_isn_tbl.id_isn_imei) AND (pos_boyas.fecha_posicion >= '20180731140755'::bpchar))
Buffers: shared hit=5444 read=21095
-> Index Scan using estados_notificaciones_imeis_isn_tbl_pkey on public.estados_notificaciones_imeis_isn_tbl estados_notify (cost=0.00..8.59 rows=1 width=31) (actual time=0.014..0.014 rows=1 loops=3015686)
Output: estados_notify.id_isn_imei, estados_notify.fecha_posicion, estados_notify.zona_prohibida, estados_notify.velocidad_lenta, estados_notify.valor_limite_velocidad_lenta, estados_notify.sin_transmision, estados_notify.comprobado, estados_notify.alerta_geofence
Index Cond: ((imeis_isn_tbl.id_isn_imei = estados_notify.id_isn_imei) AND (pos_boyas.fecha_posicion = estados_notify.fecha_posicion))
Buffers: shared hit=9572201 read=2409343 dirtied=6 written=797
Total runtime: 215291.446 ms
Best Answer
First of all, I don't think I would use that graphical visualizer tool in the first place. It seems to obfuscate more than it clarifies.
If you trace the "badge" down through the nodes to the bottom-most-one, it seems to correspond to this line from the text entry:
The index condition here is equating the indexed field, to a field in some other table. It is using a generic estimate, that a row from the other table has 15 entries in this table. But the specific row in that other table you actually have found ('whatever') has 1982 rows in this table. I don't think there is any way to get it to use the specific estimate in this case. Other than to manually query for
public.usuarios_controllers_tbl.id_usuario_controllerwhereusuario_controller = 'whatever'and then write the value of that into your outer query, which of course is highly undesirable. You might want to try it and see what would happen, as way of narrowing down the problem.But, it isn't really clear that a better estimate would lead to a better plan, anyway. Perhaps it could replace the "Bitmap Heap Scan on
public.traza_boyas_tbl pos_boyas" with a hash join instead. But it isn't clear 1) that it would do that and 2) that doing that would actually make things faster.