pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] Why is it not using an index?
11+ messages / 7 participants
[nested] [flat]
* Re: [SQL] Why is it not using an index?
@ 2002-03-15 18:18 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Dmitry Tkach <dmitry@openratings.com>
2 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2002-03-15 18:18 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 18:28 Gregory Wood <gregw@com-stock.com>
parent: Dmitry Tkach <dmitry@openratings.com>
2 siblings, 2 replies; 11+ messages in thread
From: Gregory Wood @ 2002-03-15 18:28 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: PostgreSQL-General <pgsql-general@postgresql.org>
> 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Why is it not using an index?
@ 2002-03-15 19:07 Dmitry Tkach <dmitry@openratings.com>
0 siblings, 3 replies; 11+ messages in thread
From: Dmitry Tkach @ 2002-03-15 19:07 UTC (permalink / raw)
To: pgsql-general@postgresql.org.pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 19:20 Gregory Wood <gregw@com-stock.com>
parent: Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 11+ messages in thread
From: Gregory Wood @ 2002-03-15 19:20 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: PostgreSQL-General <pgsql-general@postgresql.org>
> 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...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 20:01 Dmitry Tkach <dmitry@openratings.com>
parent: Gregory Wood <gregw@com-stock.com>
1 sibling, 2 replies; 11+ messages in thread
From: Dmitry Tkach @ 2002-03-15 20:01 UTC (permalink / raw)
To: Gregory Wood <gregw@com-stock.com>; +Cc: PostgreSQL-General <pgsql-general@postgresql.org>
<!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>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 20:12 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2002-03-15 20:12 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: Gregory Wood <gregw@com-stock.com>; PostgreSQL-General <pgsql-general@postgresql.org>
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)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 20:25 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Gregory Wood <gregw@com-stock.com>
1 sibling, 2 replies; 11+ messages in thread
From: Jean-Luc Lachance @ 2002-03-15 20:25 UTC (permalink / raw)
To: Gregory Wood <gregw@com-stock.com>; +Cc: Dmitry Tkach <dmitry@openratings.com>; PostgreSQL-General <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 20:34 Gregory Wood <gregw@com-stock.com>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 1 reply; 11+ messages in thread
From: Gregory Wood @ 2002-03-15 20:34 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: PostgreSQL-General <pgsql-general@postgresql.org>
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-15 21:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gregory Wood <gregw@com-stock.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2002-03-15 21:00 UTC (permalink / raw)
To: Gregory Wood <gregw@com-stock.com>; +Cc: Jean-Luc Lachance <jllachan@nsd.ca>; PostgreSQL-General <pgsql-general@postgresql.org>
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-16 00:03 Sergio Freue <sfreue@yahoo.com>
parent: Dmitry Tkach <dmitry@openratings.com>
2 siblings, 0 replies; 11+ messages in thread
From: Sergio Freue @ 2002-03-16 00:03 UTC (permalink / raw)
To: pgsql-general@postgresql.org.pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why is it not using an index?
@ 2002-03-17 23:55 Martijn van Oosterhout <kleptog@svana.org>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 11+ messages in thread
From: Martijn van Oosterhout @ 2002-03-17 23:55 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: Gregory Wood <gregw@com-stock.com>; Dmitry Tkach <dmitry@openratings.com>; PostgreSQL-General <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2002-03-17 23:55 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-15 19:07 Why is it not using an index? Dmitry Tkach <dmitry@openratings.com>
2002-03-15 18:18 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-15 18:28 ` Gregory Wood <gregw@com-stock.com>
2002-03-15 20:01 ` Dmitry Tkach <dmitry@openratings.com>
2002-03-15 19:20 ` Gregory Wood <gregw@com-stock.com>
2002-03-15 20:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-15 20:25 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-03-15 20:34 ` Gregory Wood <gregw@com-stock.com>
2002-03-15 21:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-17 23:55 ` Martijn van Oosterhout <kleptog@svana.org>
2002-03-16 00:03 ` Sergio Freue <sfreue@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox