Skip site navigation (1) Skip section navigation (2)

Re: Problema uso de indices...

From: motum hesa <motums(at)gmail(dot)com>
To: Lista PostgreSQL <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: Re: Problema uso de indices...
Date: 2011-08-31 22:31:12
Message-ID: CAJu20Ah-HAZE+Ct=g1YEFAb7NHt75JyzMmCO1Atvr6-NRm4jvw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
> 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

In response to

pgsql-es-ayuda by date

Next:From: Miguel Angel Hernandez MorenoDate: 2011-08-31 23:24:04
Subject: Re: pregunta sobre consulta de postgres
Previous:From: Alvaro HerreraDate: 2011-08-31 20:29:47
Subject: Re: pregunta sobre consulta de postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group