pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
simple select-statement takes more than 25 sec
5+ messages / 3 participants
[nested] [flat]

* simple select-statement takes more than 25 sec
@ 2004-11-10 09:35 Cao Duy <cao.duy@1und1.com>
  2004-11-10 10:17 ` Re: simple select-statement takes more than 25 sec Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-11-10 10:31 ` Re: simple select-statement takes more than 25 sec gnari <gnari@simnet.is>
  0 siblings, 2 replies; 5+ messages in thread

From: Cao Duy @ 2004-11-10 09:35 UTC (permalink / raw)
  To: pgsql-performance

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;




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: simple select-statement takes more than 25 sec
  2004-11-10 09:35 simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
@ 2004-11-10 10:17 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-11-10 11:22   ` Re: simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Steinar H. Gunderson @ 2004-11-10 10:17 UTC (permalink / raw)
  To: pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: simple select-statement takes more than 25 sec
  2004-11-10 09:35 simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
  2004-11-10 10:17 ` Re: simple select-statement takes more than 25 sec Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2004-11-10 11:22   ` Cao Duy <cao.duy@1und1.com>
  2004-11-10 11:45     ` Re: simple select-statement takes more than 25 sec Steinar H. Gunderson <sgunderson@bigfoot.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Cao Duy @ 2004-11-10 11:22 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: simple select-statement takes more than 25 sec
  2004-11-10 09:35 simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
  2004-11-10 10:17 ` Re: simple select-statement takes more than 25 sec Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-11-10 11:22   ` Re: simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
@ 2004-11-10 11:45     ` Steinar H. Gunderson <sgunderson@bigfoot.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Steinar H. Gunderson @ 2004-11-10 11:45 UTC (permalink / raw)
  To: pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: simple select-statement takes more than 25 sec
  2004-11-10 09:35 simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
@ 2004-11-10 10:31 ` gnari <gnari@simnet.is>
  1 sibling, 0 replies; 5+ messages in thread

From: gnari @ 2004-11-10 10:31 UTC (permalink / raw)
  To: Cao Duy <cao.duy@1und1.com>; pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-11-10 11:45 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-10 09:35 simple select-statement takes more than 25 sec Cao Duy <cao.duy@1und1.com>
2004-11-10 10:17 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-11-10 11:22   ` Cao Duy <cao.duy@1und1.com>
2004-11-10 11:45     ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-11-10 10:31 ` gnari <gnari@simnet.is>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox