Hi, The table has 43 columns. I have attached the columns-list.They have many char() and numeric columns. For the table size, these are the corresponding entries from the pg_class foo is the table and the others are some of its indexes.
relname reltuples relpages foo 2.9384E7 825699 foo_idx_pat 2.9384E7 684995 foo_idx_service 2.9384E7 433549 foo_idx_serv 2.9384E7 433435 foo_pk 2.9384E7 109057 Thanks Sharmila ----- Original Message ---- From: Gregory Stark <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: Pavel Stehule <[EMAIL PROTECTED]>; pgsql-general@postgresql.org Sent: Tuesday, November 6, 2007 8:03:48 PM Subject: Re: [GENERAL] Postgresql simple query performance question "SHARMILA JOTHIRAJAH" <[EMAIL PROTECTED]> writes: > I understand that. But why is that when oracle is given a hint to do full > table scan instead of using index to get the count, it is still faster than > postgres when both has the same explain plan? Oracle takes 34 sec and > postgres takes 1 m10 sec . Is there anything that can be done in postgresql > for speeding this up? How large are the actual respective data files? What are the columns in these tables? Do you have many char() and NUMERIC columns? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
rmrs=# \d foo Column | Type | Modifiers -------------------------------+-----------------------------+--------------- foo_id | numeric(22,0) | not null institution_id | numeric(22,0) | not null patient_id | numeric(22,0) | not null top_parent_service_code | character varying(40) | not null top_parent_service_sys_id | numeric(22,0) | not null physiologic_time | timestamp without time zone | not null top_parent_filler_order_id | numeric(22,0) | dewey_decimal_tree_sort_order | character varying(100) | not null sister_sequence_number | numeric(22,0) | service_code | character varying(40) | not null service_sys_id | numeric(22,0) | not null filler_order_id | numeric(22,0) | immediate_variable_id | numeric(22,0) | data_arrival_time | timestamp without time zone | default now() specimen_id | numeric(22,0) | value_type | character varying(40) | value_text_for_display | character varying(1010) | value_modifier_text | character varying(1000) | value_if_type_is_coded_code | character varying(40) | value_if_type_is_coded_sys_id | numeric(22,0) | value_if_type_is_numeric | double precision | value_if_type_is_provider_id | numeric(22,0) | value_if_type_is_location_id | numeric(22,0) | value_if_type_is_time | timestamp without time zone | status_code | character varying(40) | clinical_status_code | character varying(40) | interpretation_code | character varying(40) | off_scale_exception_code | character varying(40) | delta_check_code | character varying(40) | producer_application_id | numeric(22,0) | producer_location_id | numeric(22,0) | origination_code | character varying(40) | delivering_message_id | numeric(22,0) | deliv_sub_id | character varying(40) | deliv_value | character varying(100) | deliv_value_text | character varying(1000) | deliv_value_code_system | character varying(100) | value_quantitative_form | character varying(40) | deliv_service_code | character varying(40) | deliv_service_code_text | character varying(100) | deliv_service_code_system | character varying(40) | deliv_unit_code | character varying(40) | deliv_unit_text | character varying(100) | deliv_unit_code_system | character varying(40) | deliv_normal_range | character varying(100) | normal_range_text | character varying(100) | value_modifier_text_type_code | character varying(40) |
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/