Re: Postgresql simple query performance question

[SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>]

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 <stark@xxxxxxxxxxxxxxxx>
To: SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>
Cc: Pavel Stehule <pavel.stehule@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Sent: Tuesday, November 6, 2007 8:03:48 PM
Subject: Re: Postgresql simple query performance question

"SHARMILA JOTHIRAJAH" <sharmi_jo@xxxxxxxxx> 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/