Thread: I could not get postgres to utilizy indexes

I could not get postgres to utilizy indexes

From

"Igor Artimenko"

Date:

18 August 2004, 15:52:44

Hi verybody!

I can't make use of indexes even I tried the same test by changing different settings in postgres.conf like geqo to
off/on& geqo related parameters, enable_seqscan off/on & so on. Result is the same.  

Here is test itself:

I've created simplest table test and executed the same statement "explain analyze select id from test where id =
50000;"Few times I added 100,000 records, applied vacuum full; and issued above explain command.  
Postgres uses sequential scan instead of index one.
Of cause Time to execute the same statement constantly grows. In my mind index should not allow time to grow so much.

Why Postgres does not utilizes primary unique index?
What I'm missing? It continue growing even there are 1,200,000 records. It should at least start using index at some
point.


Details are below:
100,000 records:
QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..2427.00 rows=2 width=8) (actual time=99.626..199.835 rows=1 loops=1)
   Filter: (id = 50000)
 Total runtime: 199.990 ms

200,000 records:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..4853.00 rows=2 width=8) (actual time=100.389..402.770 rows=1 loops=1)
   Filter: (id = 50000)
 Total runtime: 402.926 ms


300,000 records:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..7280.00 rows=1 width=8) (actual time=100.563..616.064 rows=1 loops=1)
   Filter: (id = 50000)
 Total runtime: 616.224 ms
(3 rows)

I've created test table by script:

CREATE TABLE test
(
  id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
  description char(50),
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE next_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 10000000000
  START 1
  CACHE 5
  CYCLE;

I use postgres 7.4.2

Re: I could not get postgres to utilizy indexes

From

Thomas Swan

Date:

18 August 2004, 17:12:19

Igor Artimenko wrote:

>Hi verybody!
>
>I can't make use of indexes even I tried the same test by changing different settings in postgres.conf like geqo to
off/on& geqo related parameters, enable_seqscan off/on & so on. Result is the same.  
>
>Here is test itself:
>
>I've created simplest table test and executed the same statement "explain analyze select id from test where id =
50000;"Few times I added 100,000 records, applied vacuum full; and issued above explain command.  
>Postgres uses sequential scan instead of index one.
>Of cause Time to execute the same statement constantly grows. In my mind index should not allow time to grow so much.

>
>Why Postgres does not utilizes primary unique index?
>What I'm missing? It continue growing even there are 1,200,000 records. It should at least start using index at some
point.
>
>
>
Igor, you may want to run "vacuum analyze" and see if your results change.

Thomas

Re: I could not get postgres to utilizy indexes

From

"gnari"

Date:

18 August 2004, 17:24:48

"Thomas Swan" <tswan@idigx.com> says:


> Igor Artimenko wrote:
>

[ snipped question that was almost exactly a repeat
  of one we saw yesterday ]

> >
> >
> Igor, you may want to run "vacuum analyze" and see if your results change.

Actually, I think it was determined that the problem was due to the
int index

Michal Taborsky suggested this solution:

    select id from test where id = 50000::int8

did this not help ?

gnari

Re: I could not get postgres to utilizy indexes

From

"gnari"

Date:

18 August 2004, 17:29:21

I ("gnari" <gnari@simnet.is>) miswrote:

> Actually, I think it was determined that the problem was due to the
> int index

of course, i meant int8 index

gnari

Re: I could not get postgres to utilizy indexes

From

"Leeuw van der, Tim"

Date:

19 August 2004, 07:55:10

Hi,

You asked the very same question yesterday, and I believe you got some useful answers. Why do you post the question
again?

You don't even mention your previous post, and you didn't continue the thread which you started yesterday.

Did you try out any of the suggestions which you got yesterday? Do you have further questions about, for instance, how
todo casting of values? If so, please continue posting with the previous thread, rather than reposting the same
questionwith a different subject. 

regards,

--Tim


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Igor
Artimenko
Sent: dinsdag 17 augustus 2004 16:23
To: pgsql-performance@postgresql.org
Subject: [PERFORM] I could not get postgres to utilizy indexes


Hi verybody!

I can't make use of indexes even I tried the same test by changing different settings in postgres.conf like geqo to
off/on& geqo related parameters, enable_seqscan off/on & so on. Result is the same.  

Here is test itself:

I've created simplest table test and executed the same statement "explain analyze select id from test where id =
50000;"Few times I added 100,000 records, applied vacuum full; and issued above explain command.  
Postgres uses sequential scan instead of index one.
Of cause Time to execute the same statement constantly grows. In my mind index should not allow time to grow so much.

Why Postgres does not utilizes primary unique index?
What I'm missing? It continue growing even there are 1,200,000 records. It should at least start using index at some
point.


Details are below:
100,000 records:
QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..2427.00 rows=2 width=8) (actual time=99.626..199.835 rows=1 loops=1)
   Filter: (id = 50000)
 Total runtime: 199.990 ms

200,000 records:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..4853.00 rows=2 width=8) (actual time=100.389..402.770 rows=1 loops=1)
   Filter: (id = 50000)
 Total runtime: 402.926 ms


300,000 records:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..7280.00 rows=1 width=8) (actual time=100.563..616.064 rows=1 loops=1)
   Filter: (id = 50000)
 Total runtime: 616.224 ms
(3 rows)

I've created test table by script:

CREATE TABLE test
(
  id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
  description char(50),
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE next_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 10000000000
  START 1
  CACHE 5
  CYCLE;

I use postgres 7.4.2




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Re: I could not get postgres to utilizy indexes

From

Manfred Koizar

Date:

20 August 2004, 13:36:33

On Thu, 19 Aug 2004 09:54:47 +0200, "Leeuw van der, Tim"
<tim.leeuwvander@nl.unisys.com> wrote:
>You asked the very same question yesterday, and I believe you got some useful answers. Why do you post the question
again?

Tim, no need to be rude here.  We see this effect from time to time when
a new user sends a message to a mailing list while not subscribed.  The
sender gets an automated reply from majordomo, subscribes to the list
and sends his mail again.  One or two days later the original message is
approved (by Marc, AFAIK) and forwarded to the list.  Look at the
timestamps in these header lines:
|Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
|    by svr4.postgresql.org (Postfix) with ESMTP id 32B1F5B04F4;
|    Wed, 18 Aug 2004 15:54:13 +0000 (GMT)
|Received: from localhost (unknown [200.46.204.144])
|    by svr1.postgresql.org (Postfix) with ESMTP id E6B2B5E4701
|    for <pgsql-performance-postgresql.org@localhost.postgresql.org>; Tue, 17 Aug 2004 11:23:07 -0300 (ADT)

>[more instructions]

And while we are teaching netiquette, could you please stop top-posting
and full-quoting.

Igor, welcome to the list!  Did the suggestions you got solve your
problem?

Servus
 Manfred

Re: I could not get postgres to utilizy indexes

From

"Leeuw van der, Tim"

Date:

20 August 2004, 13:45:27

Hi all,

I offered apologies to Igor Artimenko in private mail already; I'll apologize again here.

About top-posting: Outlook Exchange teaches bad habits. Can you set Outlook Exchange to prefix lines with "> " only
whenmail is in text-only format but not when mail arrives in html / rtf format? 

About full quoting: my apologies.

-----Original Message-----
From: Manfred Koizar [mailto:mkoi-pg@aon.at]
Sent: vrijdag 20 augustus 2004 15:38
To: Leeuw van der, Tim
Cc: Igor Artimenko; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] I could not get postgres to utilizy indexes

On Thu, 19 Aug 2004 09:54:47 +0200, "Leeuw van der, Tim"
<tim.leeuwvander@nl.unisys.com> wrote:
>You asked the very same question yesterday, and I believe you got some useful answers. Why do you post the question
again?

Tim, no need to be rude here.
[...]
>[more instructions]

And while we are teaching netiquette, could you please stop top-posting
and full-quoting.

Igor, welcome to the list!  Did the suggestions you got solve your
problem?

Servus
 Manfred