Re: Problema uso de indices...

> Hm, raro. Muestra el \d de la tabla otra vez por favor, en ambos
> servidores.  Muestra también el resultado de
>

Server 1

Table "public.datosentrada_his"
Column | Type | Modifiers
---------------------+-----------------------------+-----------
fechaentrada | timestamp without time zone |
lat | double precision |
lng | double precision |
direccion | character varying |
entrandosaliendo | integer |
odometrototal | double precision |
hrastotalmotor | double precision |
hrastotalpto | double precision |
hrastotalralenti | double precision |
combustotalralenti | double precision |
combustotalpto | double precision |
bateria | double precision |
altitud | double precision |
factorcarga | double precision |
torque | double precision |
conexiontrailer1 | integer |
conexiontrailer2 | integer |
refpunto | integer |
refciudad | integer |
unitno | character varying(15) | not null
flota | character varying(40) | not null
importacionid | bigint | not null
viajeactivo | integer |
cro | integer |
ignicion | boolean |
pia | integer |
fechaenvio | timestamp without time zone |
fechacreacion | timestamp without time zone |
point_geom | geometry |
point_geomutm | geometry |
combustibletotal | double precision |
dkilometros | double precision |
dhoraspto | double precision |
dhorasmotor | double precision |
dcombustible | double precision |
velpromedio | double precision |
dhorasralenti | double precision | default 0
nalarmas | smallint | default 0
nfallas | smallint | default 0
nmensajes | smallint | default 0
tempmotor | double precision |
tempaceite | double precision |
presionturbo | double precision |
presionaceite | double precision |
rpm | integer |
tiempo_optimas_rpm | double precision | default 0
tiempo_retarder | double precision | default 0
dcombustibleralenti | double precision |
dcombustiblepto | double precision |
dtiempo_optimas_rpm | double precision |
dtiempo_retarder | double precision |
mrefp | double precision |
mrefc | double precision |
pendientes | smallint |
pos_gps | boolean |
minrpm | integer |
engrane_transmision | integer |
vel_max | double precision |
compre_break_dis | double precision |
cont_apli_fren | integer |
cont_ace_brusca | integer |
cont_desac_brusca | integer |
total_eng_cruise | double precision |
odom_virtual | double precision |
num_sate | integer |
vel_gps | integer |
edobatt | smallint |
Indexes:
"datosentrada_his_pk" PRIMARY KEY, btree (importacionid)
"fki_importacionid_his" btree (importacionid)
"fki_vehiculo_his" btree (unitno, flota)
"fki_viajeactivo_his" btree (viajeactivo)
"ind_fecha" btree (fechacreacion)
"inf_fecha_id_his" btree (fechacreacion, importacionid)
Check constraints:
"enforce_dims_point_geom" CHECK (ndims(point_geom) = 2)
"enforce_dims_point_geomutm" CHECK (ndims(point_geomutm) = 2)
"enforce_geotype_point_geom" CHECK (geometrytype(point_geom) =
'POINT'::text OR point_geom IS NULL)
"enforce_geotype_point_geomutm" CHECK (geometrytype(point_geomutm)
= 'POINT'::text OR point_geomutm IS NULL)
"enforce_srid_point_geom" CHECK (srid(point_geom) = 4326)
"enforce_srid_point_geomutm" CHECK (srid(point_geomutm) = 32614)
Foreign-key constraints:
"historico_vehiculo" FOREIGN KEY (unitno, flota) REFERENCES
vehiculos(unitno, flota) ON UPDATE CASCADE
Triggers:
pdatosentrada_his_trig BEFORE INSERT ON datosentrada_his FOR EACH
ROW EXECUTE PROCEDURE pdatosentrada_his_func()

Server 2

Table "public.datosentrada_his"
Column | Type | Modifiers
---------------------+-----------------------------+-----------
fechaentrada | timestamp without time zone |
lat | double precision |
lng | double precision |
direccion | character varying |
entrandosaliendo | integer |
odometrototal | double precision |
hrastotalmotor | double precision |
hrastotalpto | double precision |
hrastotalralenti | double precision |
combustotalralenti | double precision |
combustotalpto | double precision |
bateria | double precision |
altitud | double precision |
factorcarga | double precision |
torque | double precision |
conexiontrailer1 | integer |
conexiontrailer2 | integer |
refpunto | integer |
refciudad | integer |
unitno | character varying(15) | not null
flota | character varying(40) | not null
importacionid | bigint | not null
viajeactivo | integer |
cro | integer |
ignicion | boolean |
pia | integer |
fechaenvio | timestamp without time zone |
fechacreacion | timestamp without time zone |
point_geom | geometry |
point_geomutm | geometry |
combustibletotal | double precision |
dkilometros | double precision |
dhoraspto | double precision |
dhorasmotor | double precision |
dcombustible | double precision |
velpromedio | double precision |
dhorasralenti | double precision | default 0
nalarmas | smallint | default 0
nfallas | smallint | default 0
nmensajes | smallint | default 0
tempmotor | double precision |
tempaceite | double precision |
presionturbo | double precision |
presionaceite | double precision |
rpm | integer |
tiempo_optimas_rpm | double precision | default 0
tiempo_retarder | double precision | default 0
dcombustibleralenti | double precision |
dcombustiblepto | double precision |
dtiempo_optimas_rpm | double precision |
dtiempo_retarder | double precision |
mrefp | double precision |
mrefc | double precision |
pendientes | smallint |
pos_gps | boolean |
minrpm | integer |
engrane_transmision | integer |
vel_max | double precision |
compre_break_dis | double precision |
cont_apli_fren | integer |
cont_ace_brusca | integer |
cont_desac_brusca | integer |
total_eng_cruise | double precision |
odom_virtual | double precision |
num_sate | integer |
vel_gps | integer |
edobatt | smallint |
Indexes:
"datosentrada_his_pk" PRIMARY KEY, btree (importacionid)
"fki_importacionid_his" btree (importacionid)
"fki_vehiculo_his" btree (unitno, flota)
"fki_viajeactivo_his" btree (viajeactivo)
"ind_fecha" btree (fechacreacion)
"inf_fecha_id_his" btree (fechacreacion, importacionid)
Check constraints:
"enforce_dims_point_geom" CHECK (ndims(point_geom) = 2)
"enforce_dims_point_geomutm" CHECK (ndims(point_geomutm) = 2)
"enforce_geotype_point_geom" CHECK (geometrytype(point_geom) =
'POINT'::text OR point_geom IS NULL)
"enforce_geotype_point_geomutm" CHECK (geometrytype(point_geomutm)
= 'POINT'::text OR point_geomutm IS NULL)
"enforce_srid_point_geom" CHECK (srid(point_geom) = 4326)
"enforce_srid_point_geomutm" CHECK (srid(point_geomutm) = 32614)
Foreign-key constraints:
"historico_vehiculo" FOREIGN KEY (unitno, flota) REFERENCES
vehiculos(unitno, flota) ON UPDATE CASCADE
Triggers:
pdatosentrada_his_trig BEFORE INSERT ON datosentrada_his FOR EACH
ROW EXECUTE PROCEDURE pdatosentrada_his_func()

> select name, setting, source from pg_settings where source <> 'default';
>

Server 1

select name, setting, source from pg_settings where source <> 'default';
name | setting |
source
----------------------------+---------------------------------------+----------------------
checkpoint_segments | 16 |
configuration file
client_min_messages | notice |
configuration file
config_file | /usr/local/pgsql/data/postgresql.conf | override
constraint_exclusion | on |
configuration file
data_directory | /usr/local/pgsql/data | override
DateStyle | ISO, MDY |
configuration file
default_text_search_config | pg_catalog.english |
configuration file
effective_cache_size | 524288 |
configuration file
hba_file | /usr/local/pgsql/data/pg_hba.conf | override
ident_file | /usr/local/pgsql/data/pg_ident.conf | override
lc_collate | C | override
lc_ctype | C | override
lc_messages | C |
configuration file
lc_monetary | C |
configuration file
lc_numeric | C |
configuration file
lc_time | C |
configuration file
listen_addresses | 10.2.130.10, 67.205.87.65 |
configuration file
log_destination | stderr |
configuration file
log_directory | pg_log |
configuration file
log_error_verbosity | verbose |
configuration file
log_filename | postgresql-%Y-%m-%d_%H%M%S.log |
configuration file
log_line_prefix | %t : %u(at)%h %d : |
configuration file
log_min_duration_statement | 5000 |
configuration file
log_min_error_statement | error |
configuration file
log_min_messages | warning |
configuration file
log_rotation_age | 1440 |
configuration file
log_rotation_size | 10240 |
configuration file
log_timezone | Etc/UTC |
command line
log_truncate_on_rotation | on |
configuration file
logging_collector | on |
configuration file
maintenance_work_mem | 786432 |
configuration file
max_connections | 100 |
configuration file
max_locks_per_transaction | 128 |
configuration file
max_prepared_transactions | 100 |
configuration file
max_stack_depth | 2048 |
environment variable
port | 5432 |
configuration file
server_encoding | UTF8 | override
shared_buffers | 196608 |
configuration file
silent_mode | on |
configuration file
temp_buffers | 10240 |
configuration file
TimeZone | Etc/UTC |
configuration file
timezone_abbreviations | Default |
command line
transaction_isolation | read committed | override
transaction_read_only | off | override
update_process_title | off |
configuration file
work_mem | 51200 |
configuration file
(46 rows)

Server 2

# select name, setting, source from pg_settings where source <> 'default';
name | setting |
source
----------------------------+---------------------------------------+----------------------
checkpoint_segments | 16 |
configuration file
client_min_messages | notice |
configuration file
config_file | /usr/local/pgsql/data/postgresql.conf | override
constraint_exclusion | on |
configuration file
data_directory | /usr/local/pgsql/data | override
DateStyle | ISO, MDY |
configuration file
default_text_search_config | pg_catalog.english |
configuration file
effective_cache_size | 524288 |
configuration file
enable_seqscan | off | session
hba_file | /usr/local/pgsql/data/pg_hba.conf | override
ident_file | /usr/local/pgsql/data/pg_ident.conf | override
lc_collate | C | override
lc_ctype | C | override
lc_messages | C |
configuration file
lc_monetary | C |
configuration file
lc_numeric | C |
configuration file
lc_time | C |
configuration file
listen_addresses | 192.168.0.144 |
configuration file
log_destination | stderr |
configuration file
log_directory | pg_log |
configuration file
log_error_verbosity | verbose |
configuration file
log_filename | postgresql-%Y-%m-%d_%H%M%S.log |
configuration file
log_line_prefix | %t : %u(at)%h %d : |
configuration file
log_min_duration_statement | 5000 |
configuration file
log_min_error_statement | error |
configuration file
log_min_messages | warning |
configuration file
log_rotation_age | 1440 |
configuration file
log_rotation_size | 10240 |
configuration file
log_timezone | Etc/UTC |
command line
log_truncate_on_rotation | on |
configuration file
logging_collector | on |
configuration file
maintenance_work_mem | 163840 |
configuration file
max_connections | 250 |
configuration file
max_locks_per_transaction | 128 |
configuration file
max_prepared_transactions | 250 |
configuration file
max_stack_depth | 2048 |
environment variable
port | 5432 |
configuration file
server_encoding | UTF8 | override
shared_buffers | 262144 |
configuration file
silent_mode | on |
configuration file
temp_buffers | 10240 |
configuration file
TimeZone | Etc/UTC |
configuration file
timezone_abbreviations | Default |
command line
transaction_isolation | read committed | override
transaction_read_only | off | override
update_process_title | off |
configuration file
work_mem | 29696 |
configuration file
(47 rows)

> --
> Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
>

Aqui esta lo que me pediste alvaro... cualquier cosa comentame...

Roberto Campos