Date: mer mai 14 09:38:14 GMT 2008 Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga) Version Postgresql: 8.3.1 Description de la table diffusion_2008_05_13: Table « archive.diffusion_2008_05_13 » Colonne | Type | Modificateurs ---------------------------------+-----------------------------+--------------- id | bigint | not null id_product | bigint | not null diffusion_externalid | character varying(120) | subscriber_id | bigint | subscriber_name | character varying(50) | client_id | bigint | client_name | character varying(50) | check_diffusion | character varying(1) | not null priority | integer | not null format | character varying(30) | diffusion_format | character varying(30) | diffusion_standby_format | character varying(30) | auto_convert_format | character varying(30) | auto_convert_standby_format | character varying(30) | file_name | character varying(256) | not null file_size | bigint | not null format_id | bigint | not null format_part_number | integer | not null format_archive | character varying(1) | not null allow_auto_convert | character varying(1) | not null ogc_product_id | character varying(100) | rhapsodie_product_id | character varying(100) | other_product_id | character varying(100) | return_mode | character varying(10) | not null return_ftp_host | character varying(60) | return_ftp_user | character varying(100) | return_ftp_passwd | character varying(200) | return_ftp_directory | character varying(512) | return_ftp_use_size | character varying(1) | return_ftp_passive | character varying(1) | return_ftp_port | integer | return_email_adress | character varying(160) | return_email_secure_out1 | character varying(160) | return_email_secure_out2 | character varying(160) | second_return_error | character varying(1) | not null second_return_acq | character varying(1) | not null second_return_mode | character varying(10) | not null second_return_ftp_host | character varying(60) | second_return_ftp_user | character varying(100) | second_return_ftp_passwd | character varying(200) | second_return_ftp_directory | character varying(512) | second_return_ftp_use_size | character varying(1) | second_return_ftp_passive | character varying(1) | second_return_ftp_port | integer | second_return_email_adress | character varying(160) | second_return_email_secure_out1 | character varying(160) | second_return_email_secure_out2 | character varying(160) | check_cron | character varying(1) | not null cron_tz | character varying(6) | cron_minute | character varying(180) | cron_hour | character varying(72) | cron_day_of_month | character varying(93) | cron_month | character varying(36) | cron_day_of_week | character varying(24) | cron_open_delay | integer | dont_send_between_tz | character varying(6) | dont_send_between_begin | character varying(5) | dont_send_between_end | character varying(5) | channel | character varying(30) | not null media | character varying(15) | not null pdif1 | character varying | pdif2 | character varying | pdif3 | character varying | pdif4 | character varying | pdif5 | character varying | pdif6 | character varying | pdif7 | character varying | pdif8 | character varying | pdif9 | character varying | pdif10 | character varying | pdif11 | character varying | pdif12 | character varying | pdif13 | character varying | pdif14 | character varying | pdif15 | character varying | pdif16 | character varying | pdif17 | character varying | pdif18 | character varying | pdif19 | character varying | pdif20 | character varying | standby_channel | character varying(30) | standby_media | character varying(15) | switch_method_medias_ftp | character varying(15) | standby_switch_try_number | integer | standby_switch_try_date | timestamp without time zone | standby_pdif1 | character varying | standby_pdif2 | character varying | standby_pdif3 | character varying | standby_pdif4 | character varying | standby_pdif5 | character varying | standby_pdif6 | character varying | standby_pdif7 | character varying | standby_pdif8 | character varying | standby_pdif9 | character varying | standby_pdif10 | character varying | standby_pdif11 | character varying | standby_pdif12 | character varying | standby_pdif13 | character varying | standby_pdif14 | character varying | standby_pdif15 | character varying | standby_pdif16 | character varying | standby_pdif17 | character varying | standby_pdif18 | character varying | standby_pdif19 | character varying | standby_pdif20 | character varying | state | integer | not null state_date | timestamp without time zone | not null last_try_date | timestamp without time zone | next_try_date | timestamp without time zone | try_number | integer | not null next_try_channel | character varying(30) | not null next_try_key | character varying(2048) | not null next_try_is_main_channel | character varying(1) | not null diffusion_date | timestamp without time zone | provider_diffusion_date | timestamp without time zone | diffusion_size | bigint | not null diffusion_time | integer | diffusion_format_id | bigint | not null diffusion_format_part_number | integer | not null diffusion_log | character varying | Index : « pk_diffusion_2008_05_13_id » PRIMARY KEY, btree (id) « indx_diffusion_2008_05_13_channel » btree (state, next_try_channel varchar_pattern_ops, next_try_key varchar_pattern_ops, priority, next_try_date) « indx_diffusion_2008_05_13_id_product » btree (id_product), tablespace « archive » « indx_diffusion_2008_05_13_idx1 » btree (next_try_channel, state, next_try_key), tablespace « archive » « indx_diffusion_2008_05_13_state_format » btree (state, format varchar_pattern_ops, priority, state_date), tablespace « archive » « indx_diffusion_2008_05_13_subscriber_name » btree (lower(subscriber_name::text) varchar_pattern_ops), tablespace « archive » Contraintes de vérification : « ck_diffusion_2008_05_13_allow_auto_convert » CHECK (allow_auto_convert::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[])) « ck_diffusion_2008_05_13_check_cron » CHECK (check_cron::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[])) « ck_diffusion_2008_05_13_check_diffusion » CHECK (check_diffusion::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[])) « ck_diffusion_2008_05_13_cron_tz » CHECK (cron_tz::text ~ '^([+-][01][0-9]:[0-5][0-9]|[+-]2[0-3]:[0-5][0-9])$'::text) « ck_diffusion_2008_05_13_diffusion_externalid » CHECK (diffusion_externalid::text ~ '^[a-zA-Z0-9][a-zA-Z0-9_,+\\.-]+$'::text) « ck_diffusion_2008_05_13_diffusion_format_id » CHECK (diffusion_format_id > 0) « ck_diffusion_2008_05_13_format_archive » CHECK (format_archive::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[])) « ck_diffusion_2008_05_13_format_id » CHECK (format_id > 0) « ck_diffusion_2008_05_13_id » CHECK (id > 0) « ck_diffusion_2008_05_13_next_try_is_main_channel » CHECK (next_try_is_main_channel::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[])) « ck_diffusion_2008_05_13_priority » CHECK (priority >= 81 AND priority <= 89) « ck_diffusion_2008_05_13_return_mode » CHECK (return_mode::text = ANY (ARRAY['FTP'::character varying, 'EMAIL'::character varying, 'NONE'::character varying]::text[])) « ck_diffusion_2008_05_13_second_return_mode » CHECK (second_return_mode::text = ANY (ARRAY['NONE'::character varying, 'FTP'::character varying, 'EMAIL'::character varying]::text[])) « ck_diffusion_2008_05_13_state » CHECK (state > 0) Contraintes de clés étrangères : « fk_diffusion_2008_05_13_id_product » FOREIGN KEY (id_product) REFERENCES product_2008_05_13(id) Tablespace : « archive » ================================================================================ prompt-unix> psql difmet archive Bienvenue dans psql 8.3.1, l'interface interactive de PostgreSQL. difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.41 rows=1 width=7379) (actual time=12.712..169200.761 rows=48041 loops=1) Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text)) Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text)) Total runtime: 858616.959 ms (4 lignes) difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=11440.118..11440.118 rows=0 loops=1) Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text)) Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text)) Total runtime: 11440.215 ms (4 lignes) difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=365160.217..365160.217 rows=0 loops=1) Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text)) Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text)) Total runtime: 365160.313 ms (4 lignes) difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=2.859..2.859 rows=0 loops=1) Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text)) Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text)) Total runtime: 2.954 ms (4 lignes) difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=2.591..2.591 rows=0 loops=1) Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text)) Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text)) Total runtime: 2.680 ms (4 lignes)