Обсуждение: 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_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
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_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; You'll need to cast the 3 into smallint explicitly, either 3::smallint or CAST(3 as smallint) should work.
> 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_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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Gregory Wood wrote:<br /><blockquote cite="mid:002801c1cc4f$25dba980$7889ffcc@comstock.com" type="cite"><blockquote type="cite"><prewrap="">explain select * from a where x=3;<br /></pre></blockquote><pre wrap=""><br />PostgreSQL is treating3 as an int4 (integer) type, whereas x is an int2<br />(smallint) type. Try casting the constant as a smallint andit 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.04rows=10110004 width=2)<br /><br /> Am I missing something here again, or will it just notuse an index for aggregation?<br /><br /> I mean, especially an this case, it looks so weird that it KNOWS the answerto my query RIGHT AWAY (rows=... in the explain response), yet it takes it so long to return it...<br /><br /><br /><br/>
> 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...
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_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 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
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_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 > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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)
"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_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 > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > 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 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 insertedrow, 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, perhapsmore 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