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
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.