Thread: simple select-statement takes more than 25 sec

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;

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/

From: "Cao Duy" <cao.duy@1und1.com>
>
> 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

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 */

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/