Why is it not using an index?
On Fri, 15 Mar 2002, Dmitry Tkach wrote:
This must be really simple, but I just can't get it :-(
I have a table (a) with a single column (x):Table "a" Attribute | Type | Modifier -----------+----------+---------- x | smallint | Index: a_idxIndex "a_idx"
Attribute | Type
-----------+----------
x | smallint
btreeThe table has 10000000 rows....
Now, how come, when I do:
explain select * from a where x=3;
You'll need to cast the 3 into smallint explicitly, either
3::smallint or CAST(3 as smallint) should work.
Import Notes
Reply to msg id not found: 3C924680.4090806@openratings.com
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?
Show quoted text
This must be really simple, but I just can't get it :-(
I have a table (a) with a single column (x):Table "a" Attribute | Type | Modifier -----------+----------+---------- x | smallint | Index: a_idxIndex "a_idx"
Attribute | Type
-----------+----------
x | smallint
btreeThe table has 10000000 rows....
Now, how come, when I do:
explain select * from a where x=3;
it says:
Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2)
Why is it not using a_idx???
I even tried set enable_seqscan to off - makes no difference :-(
Any idea what is going on?
Thanks a lot!
Dima
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Import Notes
Reference msg id not found: 3C924680.4090806@openratings.com | Resolved by subject fallback
This must be really simple, but I just can't get it :-(
I have a table (a) with a single column (x):
Table "a"
Attribute | Type | Modifier
-----------+----------+----------
x | smallint |
Index: a_idx
Index "a_idx"
Attribute | Type
-----------+----------
x | smallint
btree
The table has 10000000 rows....
Now, how come, when I do:
explain select * from a where x=3;
it says:
Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2)
Why is it not using a_idx???
I even tried set enable_seqscan to off - makes no difference :-(
Any idea what is going on?
Thanks a lot!
Dima
Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2)
I am sorry - it says "Seq Scan on a...", of course,
not "... on bset ..."
Dima
Am I missing something here again, or will it just not use an index for
aggregation?
PostgreSQL does not use an index to perform a full table count. I'm not sure
of the exact reasoning behind this, but I think there are multiple issues
with the approach.
I mean, especially an this case, it looks so weird that it KNOWS the
answer to my query RIGHT AWAY (rows=... in the explain response), yet it
takes it so long to return it...
Actually, that rows= count is the *estimate* for the number of rows. That
estimate is calculated from a variety of statistics compiled when the
ANALYZE command is performed. Those statistics may or may not be up to date,
and are only used to plan the query's execution.
Greg
----- Original Message -----
From: Dmitry Tkach
To: Gregory Wood
Cc: PostgreSQL-General
Sent: Friday, March 15, 2002 3:01 PM
Subject: Re: [GENERAL] Why is it not using an index?
Gregory Wood wrote:
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
usethe index:explain select * from a where x=3::smallint;
Aha! Great! Thanks a lot! That worked!
Now, the next problem:
explain select count (x) from a ;
Aggregate (cost=100175934.05..100175934.05 rows=1 width=2)
-> Seq Scan on a (cost=100000000.00..100150659.04 rows=10110004 width=2)
Am I missing something here again, or will it just not use an index for
aggregation?
I mean, especially an this case, it looks so weird that it KNOWS the answer
to my query RIGHT AWAY (rows=... in the explain response), yet it takes it
so long to return it...
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
Gregory Wood wrote:<br>
<blockquote type="cite" cite="mid:002801c1cc4f$25dba980$7889ffcc@comstock.com">
<blockquote type="cite">
<pre wrap="">explain select * from a where x=3;<br></pre>
</blockquote>
<pre wrap=""><!----><br>PostgreSQL is treating 3 as an int4 (integer) type, whereas x is an int2<br>(smallint) type. Try casting the constant as a smallint and it should use<br>the index:<br><br>explain select * from a where x=3::smallint;<br><br></pre>
</blockquote>
Aha! Great! Thanks a lot! That worked!<br>
Now, the next problem:<br>
<br>
explain select count (x) from a ;<br>
<br>
Aggregate (cost=100175934.05..100175934.05 rows=1 width=2)<br>
-> Seq Scan on a (cost=100000000.00..100150659.04 rows=10110004 width=2)<br>
<br>
Am I missing something here again, or will it just not use an index for aggregation?<br>
<br>
I mean, especially an this case, it looks so weird that it KNOWS the answer
to my query RIGHT AWAY (rows=... in the explain response), yet it takes it
so long to return it...<br>
<br>
<br>
<br>
</body>
</html>
On Fri, 15 Mar 2002, Dmitry Tkach wrote:
explain select count (x) from a ;
Aggregate� (cost=100175934.05..100175934.05 rows=1 width=2)
� ->� Seq Scan on a� (cost=100000000.00..100150659.04 rows=10110004 width=2)Am I missing something here again, or will it just not use an index
for aggregation?
It won't for something like the above because it needs to test each row
to see if it's currently visible to your transaction (which involves
reading from the table file anyway) which means you end up reading the
entire table plus the index (and paying some costs in random access).
If the index had the transaction information the index would be usable
but there are issues about doing that as well (you might want to check
past messages - especially ones from Tom Lane - on the subject)
Really, the PostgreSQL interpreter should be smart enough to figure this
out by itself...
Gregory Wood wrote:
Show quoted text
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?This must be really simple, but I just can't get it :-(
I have a table (a) with a single column (x):Table "a" Attribute | Type | Modifier -----------+----------+---------- x | smallint | Index: a_idxIndex "a_idx"
Attribute | Type
-----------+----------
x | smallint
btreeThe table has 10000000 rows....
Now, how come, when I do:
explain select * from a where x=3;
it says:
Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2)
Why is it not using a_idx???
I even tried set enable_seqscan to off - makes no difference :-(
Any idea what is going on?
Thanks a lot!
Dima
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
It should be. I think the response you'll get from the people on this list
is that they're happy to accept a patch...
Greg
----- Original Message -----
From: "Jean-Luc Lachance" <jllachan@nsd.ca>
To: "Gregory Wood" <gregw@com-stock.com>
Cc: "Dmitry Tkach" <dmitry@openratings.com>; "PostgreSQL-General"
<pgsql-general@postgresql.org>
Sent: Friday, March 15, 2002 3:25 PM
Subject: Re: [GENERAL] Why is it not using an index?
Really, the PostgreSQL interpreter should be smart enough to figure this
out by itself...Gregory Wood wrote:
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?This must be really simple, but I just can't get it :-(
I have a table (a) with a single column (x):Table "a" Attribute | Type | Modifier -----------+----------+---------- x | smallint | Index: a_idxIndex "a_idx"
Attribute | Type
-----------+----------
x | smallint
btreeThe table has 10000000 rows....
Now, how come, when I do:
explain select * from a where x=3;
it says:
Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2)
Why is it not using a_idx???
I even tried set enable_seqscan to off - makes no difference :-(
Any idea what is going on?
Thanks a lot!
Dima
---------------------------(end of
broadcast)---------------------------
Show quoted text
TIP 5: Have you checked our extensive FAQ?
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
"Gregory Wood" <gregw@com-stock.com> writes:
It should be. I think the response you'll get from the people on this list
is that they're happy to accept a patch...
It's not as easy as you might think to come up with a general-purpose
solution --- bearing in mind that Postgres is supposed to support an
extensible set of datatypes, and so we'd prefer not to hard-wire much
knowledge of specific datatypes into the parser.
If you look back a year or two in the pghackers archives, you'll find
previous discussions and failed solution proposals. It's still on the
TODO list, and eventually someone will come up with a usable answer.
regards, tom lane
I had the same problem and the question was answered here yesterday:
explain select * from a where x=3;
Try
explain select * from a where x=3::smallint;
That should do it. I opted for changing all indexed SMALLINT fields to
INTEGER.
On Fri, Mar 15, 2002 at 03:25:47PM -0500, Jean-Luc Lachance wrote:
Really, the PostgreSQL interpreter should be smart enough to figure this
out by itself...
It is actually, if you put quotes around the number so it is explicitly
typed as 'unknown'. The interpreter will then accuratly match the type.
Without the quotes the number becomes int4 and so a whole promotion/type
hierarchy needs to be built to determine how to relate them.
Just put quotes around all your constants and all your problems are solved.
Gregory Wood wrote:
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?This must be really simple, but I just can't get it :-(
I have a table (a) with a single column (x):Table "a" Attribute | Type | Modifier -----------+----------+---------- x | smallint | Index: a_idxIndex "a_idx"
Attribute | Type
-----------+----------
x | smallint
btreeThe table has 10000000 rows....
Now, how come, when I do:
explain select * from a where x=3;
it says:
Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2)
Why is it not using a_idx???
I even tried set enable_seqscan to off - makes no difference :-(
Any idea what is going on?
Thanks a lot!
Dima
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Ignorance continues to thrive when intelligent people choose to do
nothing. Speaking out against censorship and ignorance is the imperative
of all intelligent people.
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 querySELECT * 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 querySELECT * 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?
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?
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