Re: [SQL] Why is it not using an index?

explain select * from a where x=3;

PostgreSQL is treating 3 as an int4 (integer) type, whereas x is an int2
(smallint) type. Try casting the constant as a smallint and it should use
the index:

explain select * from a where x=3::smallint;

Greg

----- Original Message -----
From: "Dmitry Tkach" <dmitry@openratings.com>
To: <pgsql-general@postgresql.org.pgsql-sql@postgresql.org>
Sent: Friday, March 15, 2002 2:07 PM
Subject: [GENERAL] Why is it not using an index?

I am having issues with an Index.
Here is the query

SELECT * FROM phonecalls WHERE istatus = 0 AND (sfor = 'pat' OR
sfor='TECHIES') ORDER BY ipri DESC, dplaceddate;
I have tried '0' and jut plain 0 as well as type casting it with ::int4 .

Here is the index I think it should use.

CREATE INDEX phonecallspoll ON phonecalls USING btree (sfor varchar_ops,
istatus int4_ops);
But it is still doing a scan?

Any suggestions?
yes I have used Vacumme with the anylise option.
Thanks for any info.

On Tue, 19 Mar 2002, David Siebert wrote:

I am having issues with an Index.
Here is the query

SELECT * FROM phonecalls WHERE istatus = 0 AND (sfor = 'pat' OR
sfor='TECHIES') ORDER BY ipri DESC, dplaceddate;
I have tried '0' and jut plain 0 as well as type casting it with ::int4 .

Here is the index I think it should use.

CREATE INDEX phonecallspoll ON phonecalls USING btree (sfor varchar_ops,
istatus int4_ops);
But it is still doing a scan?

Any suggestions?
yes I have used Vacumme with the anylise option.

What is the schema (probably not meaningful but always helps), what does
explain show for the query (specifically for the row counds), does using
set enable_seqscan=off change the explain output?

Here is the explain output

Sort (cost=293.24..293.24 rows=1 width=128)
-> Seq Scan on phonecalls (cost=0.00..293.23 rows=1 width=128)

Here is the Table
CREATE TABLE "phonecalls" (
"irecnum" int4 DEFAULT nextval('"phonecalls_irecnum_seq"'::text) NOT NULL,
"scaller" varchar(80),
"sphone" varchar(40),
"sphone2" varchar(40),
"squedby" varchar(40),
"sfor" varchar(40),
"dplaceddate" timestamp,
"dtakendate" timestamp,
"dresdate" timestamp,
"ipri" int4,
"istatus" int4,
"iresolution" int4,
"ireques" int4,
"snotes" varchar(3999),
"stakenby" varchar(40),
CONSTRAINT "phonecalls_irecnum_key" UNIQUE ("irecnum")
);

Where would one find set enable_seqscan=off; in the docs?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: Tuesday, March 19, 2002 3:51 PM
To: David Siebert
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Yet another indexing issue.

On Tue, 19 Mar 2002, David Siebert wrote:

I am having issues with an Index.
Here is the query

SELECT * FROM phonecalls WHERE istatus = 0 AND (sfor = 'pat' OR
sfor='TECHIES') ORDER BY ipri DESC, dplaceddate;
I have tried '0' and jut plain 0 as well as type casting it with ::int4 .

Here is the index I think it should use.

CREATE INDEX phonecallspoll ON phonecalls USING btree (sfor varchar_ops,
istatus int4_ops);
But it is still doing a scan?

Any suggestions?
yes I have used Vacumme with the anylise option.

What is the schema (probably not meaningful but always helps), what does
explain show for the query (specifically for the row counds), does using
set enable_seqscan=off change the explain output?

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

http://www.postgresql.org/users-lounge/docs/faq.html

On Tue, 19 Mar 2002, David Siebert wrote:

Here is the explain output

Sort (cost=293.24..293.24 rows=1 width=128)
-> Seq Scan on phonecalls (cost=0.00..293.23 rows=1 width=128)

Here is the Table
CREATE TABLE "phonecalls" (
"irecnum" int4 DEFAULT nextval('"phonecalls_irecnum_seq"'::text) NOT NULL,
"scaller" varchar(80),
"sphone" varchar(40),
"sphone2" varchar(40),
"squedby" varchar(40),
"sfor" varchar(40),
"dplaceddate" timestamp,
"dtakendate" timestamp,
"dresdate" timestamp,
"ipri" int4,
"istatus" int4,
"iresolution" int4,
"ireques" int4,
"snotes" varchar(3999),
"stakenby" varchar(40),
CONSTRAINT "phonecalls_irecnum_key" UNIQUE ("irecnum")
);

Where would one find set enable_seqscan=off; in the docs?

To be honest I'm not sure where it's mentioned. It's a big tool that
lets you make some gross changes to the optimizer's planning (sets the
cost of sequence scan very very high).

Does doing it before the explain change the output?

Also, does making an index on phonecalls(istatus, sfor) [rather than
sfor,istatus] change the choice?

Where would one find set enable_seqscan=off; in the docs?

To be honest I'm not sure where it's mentioned. It's a big tool that
lets you make some gross changes to the optimizer's planning (sets the
cost of sequence scan very very high).

I found this in Admin.pdf
3.4.1. Planner and Optimizer Tuning
page 37/38
ENABLE_SEQSCAN (boolean)
Enables or disables the query planner's use of sequential scan plan
types. It's not possible to
suppress sequential scans entirely, but turning this variable off
discourages the planner from using
one if there is any other method available. The default is on. This is
mostly useful to debug the
query planner.

--
Best regards,
Aplication Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@rdsor.ro

Thank you. I found the error. It was the order of the fields in the index.
I do not rember seeing that the order made a difference in the indexing. It
is logical now that I think of it.
I for the life of me could not find where in PgAdminII I could set the order
of the fields in when creating an index so I did it by hand.
Thanks again.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Pirtea Calin
Sent: Friday, March 22, 2002 9:38 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Yet another indexing issue.

Where would one find set enable_seqscan=off; in the docs?

To be honest I'm not sure where it's mentioned. It's a big tool that
lets you make some gross changes to the optimizer's planning (sets the
cost of sequence scan very very high).

I found this in Admin.pdf
3.4.1. Planner and Optimizer Tuning
page 37/38
ENABLE_SEQSCAN (boolean)
Enables or disables the query planner's use of sequential scan plan
types. It's not possible to
suppress sequential scans entirely, but turning this variable off
discourages the planner from using
one if there is any other method available. The default is on. This is
mostly useful to debug the
query planner.

--
Best regards,
Aplication Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@rdsor.ro

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

http://www.postgresql.org/users-lounge/docs/faq.html

I recently moved from 7.1.3 to 7.2.1. In doing so, my application broke. It relies on INSERT returning the OID of the inserted row, even if the INSERT was on a view. I saw a note in the 7.2.1 notes about fixing a problem in this area, perhaps more needs to be done?

Example:

acropolis=# create table shad (a integer);
CREATE
acropolis=# create view shadview as select * from shad;
CREATE
acropolis=# create rule shadview_insert as on insert to shadview do instead insert into shad values (new.a);
CREATE

acropolis=# insert into shad values (1);
INSERT 3876425 1

acropolis=# insert into shadview values (2);
INSERT 0 0

acropolis=# select * from shad;
a
---
1
2
(2 rows)

---------------

Shouldn't the second INSERT return an OID as well?

Thanks,

-Damon

Damon Cokenias <lists@mtn-palace.com> writes:

I recently moved from 7.1.3 to 7.2.1. In doing so, my application
broke. It relies on INSERT returning the OID of the inserted row,
even if the INSERT was on a view.

I'm afraid you were relying on a coincidental artifact of the old
implementation, namely that you got back the command tag associated
with the last command to be physically executed. The current code
is careful to return the command tag associated with the original
query (here, the insert into shadview) regardless of execution order
of additional queries executed by rules. I'm not sure that we can fix
your problem without breaking other cases.

regards, tom lane