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]>; [email protected]
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/
