[PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Cao Duy 
Hi all

I have a table with ca. 4Mio Rows.

here is my simple select-statement:
SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5

the result appears after about 27 sec.

what's wrong?

the same statement on mysql takes 1 milisec.

please help

here is the structur of the table
CREATE TABLE public.customer
(
  customer_id bigserial NOT NULL,
  cooperationpartner_id int8 NOT NULL DEFAULT 0::bigint,
  maincontact_id int8 NOT NULL DEFAULT 0::bigint,
  companycontact_id int8,
  def_paymentdetails_id int8,
  def_paymentsort_id int8,
  def_invoicing_id int8,
  int_customernumber varchar(50),
  ext_customernumber varchar(50),
  CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
  CONSTRAINT customer_ibfk_1 FOREIGN KEY (cooperationpartner_id)
REFERENCES public.cooperationpartner (cooperationpartner_id) ON UPDATE
NO ACTION ON DELETE NO ACTION,
  CONSTRAINT customer_ibfk_2 FOREIGN KEY (maincontact_id) REFERENCES
public.contact (contact_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT customer_ibfk_3 FOREIGN KEY (companycontact_id) REFERENCES
public.contact (contact_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT customer_ibfk_4 FOREIGN KEY (def_paymentdetails_id)
REFERENCES public.paymentdetails (paymentdetails_id) ON UPDATE NO ACTION
ON DELETE NO ACTION,
  CONSTRAINT customer_ibfk_5 FOREIGN KEY (def_paymentsort_id) REFERENCES
public.paymentsort (paymentsort_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
  CONSTRAINT customer_ibfk_6 FOREIGN KEY (def_invoicing_id) REFERENCES
public.invoicing (invoicing_id) ON UPDATE NO ACTION ON DELETE NO ACTION
) WITH OIDS;


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Steinar H. Gunderson 
On Wed, Nov 10, 2004 at 10:35:50AM +0100, Cao Duy wrote:
 here is my simple select-statement:
 SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5

It seems like you're missing an index on customer_id. Set it to PRIMARY KEY
or do an explicit CREATE INDEX (followed by an ANALYZE) and it should be a
lot faster.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread gnari 
From: Cao Duy [EMAIL PROTECTED]
 
 here is my simple select-statement:
 SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5
 
 the result appears after about 27 sec.
 
 what's wrong?
 ...
 CREATE TABLE public.customer
 (
   customer_id bigserial NOT NULL,

you do not specify version or show us
an explain analyze, or tell us what indexes
you have, but if you want to use an index
on the bigint column customer_id, and you
are using postgres version 7.4 or less, you
need to cast your constant (5) to bigint.


try
SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5::bigint
or
SELECT * FROM CUSTOMER WHERE CUSTOMER_ID='5'

gnari



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Cao Duy 
Am Mi, den 10.11.2004 schrieb Steinar H. Gunderson um 11:17:
 On Wed, Nov 10, 2004 at 10:35:50AM +0100, Cao Duy wrote:
  here is my simple select-statement:
  SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5
 
 It seems like you're missing an index on customer_id. Set it to PRIMARY KEY
 or do an explicit CREATE INDEX (followed by an ANALYZE) and it should be a
 lot faster.
there is an index on customer_id

create table customer(
...
CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
...
)

 /* Steinar */


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Steinar H. Gunderson 
On Wed, Nov 10, 2004 at 12:22:17PM +0100, Cao Duy wrote:
 there is an index on customer_id
 
 create table customer(
 ...
 CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
 ...
 )

Oh, sorry, I missed it among all the foreign keys. :-) Anyhow, as others have
pointed out, try doing a select against 5::bigint instead of just 5 (which is
an integer).

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster