pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres 7.1.3: why does the query plan ignore indexes?
8+ messages / 4 participants
[nested] [flat]
* postgres 7.1.3: why does the query plan ignore indexes?
@ 2003-08-19 08:43 Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Marco Vezzoli @ 2003-08-19 08:43 UTC (permalink / raw)
To: pgsql-sql
Hi,
I know this question was asked many times, but please help me understand
what I am doing wrong.
I work with a postgresql 7.1.3 on Solaris 8 compiled with gcc 3.0.2
Some indexes are defined on a table
ewsweb_test=> \d measures_product
Index "measures_product"
Attribute | Type
------------+----------
product_id | smallint
btree
ewsweb_test=> \d measures_date
Index "measures_date"
Attribute | Type
-----------+--------------------------
date | timestamp with time zone
btree
but the query seems to ignore it.
ewsweb_test=> explain select zero_yield, gross from measures where
product_id=29 and date between '2003-03-12' and '2003-08-14';
NOTICE: QUERY PLAN:
Seq Scan on measures (cost=0.00..12937.76 rows=254 width=12)
EXPLAIN
I know there is an option that alter the seq scan cost but I prefer to
leave the optimizer as clean as possible.
Thanks in advence for any help.
Marco
--
Marco Vezzoli tel. +39 039 603 6852
STMicroelectronics fax. +39 039 603 5055
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
@ 2003-08-19 13:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-19 13:41 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-26 04:17 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2003-08-19 13:26 UTC (permalink / raw)
To: Marco Vezzoli <marco.vezzoli@st.com>; +Cc: pgsql-sql
Marco Vezzoli <marco.vezzoli@st.com> writes:
> ewsweb_test=> \d measures_product
> Index "measures_product"
> Attribute | Type
> ------------+----------
> product_id | smallint
^^^^^^^^
> ewsweb_test=> explain select zero_yield, gross from measures where
> product_id=29 and date between '2003-03-12' and '2003-08-14';
^^^^^^^^^^^^^
"29" is taken as an integer (int4). To get an indexscan on an int2
column, you need to explicitly cast it to int2:
product_id = 29::smallint
or you can put it in quotes and let the parser figure out the right
type:
product_id = '29'
Yes, we'd like to make this better, but there are surprisingly many
pitfalls in tinkering with the assignment of datatypes to constants...
regards, tom lane
PS: you could also consider whether it's really saving you any space to
store product_id as a smallint instead of int. Because of alignment
considerations, it very possibly isn't.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-19 13:41 ` Marco Vezzoli <marco.vezzoli@st.com>
1 sibling, 0 replies; 8+ messages in thread
From: Marco Vezzoli @ 2003-08-19 13:41 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
tgl@sss.pgh.pa.us wrote:
>
> Marco Vezzoli <marco.vezzoli@st.com> writes:
> > ewsweb_test=> \d measures_product
> > Index "measures_product"
> > Attribute | Type
> > ------------+----------
> > product_id | smallint
> ^^^^^^^^
>
> > ewsweb_test=> explain select zero_yield, gross from measures where
> > product_id=29 and date between '2003-03-12' and '2003-08-14';
> ^^^^^^^^^^^^^
>
> "29" is taken as an integer (int4). To get an indexscan on an int2
> column, you need to explicitly cast it to int2:
> product_id = 29::smallint
> or you can put it in quotes and let the parser figure out the right
> type:
> product_id = '29'
>
> Yes, we'd like to make this better, but there are surprisingly many
> pitfalls in tinkering with the assignment of datatypes to constants...
>
> regards, tom lane
>
> PS: you could also consider whether it's really saving you any space to
> store product_id as a smallint instead of int. Because of alignment
> considerations, it very possibly isn't.
thank you, now it works better; however it seems that the date index is
ignored.
ewsweb_test=> explain select zero_yield, gross from measures where
product_id=29::smallint and date between '2003-03-12' and '2003-08-14';
NOTICE: QUERY PLAN:
Index Scan using measures_product on measures (cost=0.00..3792.27
rows=254 width=12)
EXPLAIN
--
Marco Vezzoli tel. +39 039 603 6852
STMicroelectronics fax. +39 039 603 5055
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-26 04:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-26 13:09 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2003-08-26 04:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marco Vezzoli <marco.vezzoli@st.com>; pgsql-sql
Should we consider adding some warning when someone creates an index on
an int2 column?
---------------------------------------------------------------------------
Tom Lane wrote:
> Marco Vezzoli <marco.vezzoli@st.com> writes:
> > ewsweb_test=> \d measures_product
> > Index "measures_product"
> > Attribute | Type
> > ------------+----------
> > product_id | smallint
> ^^^^^^^^
>
> > ewsweb_test=> explain select zero_yield, gross from measures where
> > product_id=29 and date between '2003-03-12' and '2003-08-14';
> ^^^^^^^^^^^^^
>
> "29" is taken as an integer (int4). To get an indexscan on an int2
> column, you need to explicitly cast it to int2:
> product_id = 29::smallint
> or you can put it in quotes and let the parser figure out the right
> type:
> product_id = '29'
>
> Yes, we'd like to make this better, but there are surprisingly many
> pitfalls in tinkering with the assignment of datatypes to constants...
>
> regards, tom lane
>
> PS: you could also consider whether it's really saving you any space to
> store product_id as a smallint instead of int. Because of alignment
> considerations, it very possibly isn't.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
2003-08-26 04:17 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-26 13:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-26 23:32 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Gaetano Mendola <mendola@bigfoot.com>
2003-08-27 04:22 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2003-08-26 13:09 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Marco Vezzoli <marco.vezzoli@st.com>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Should we consider adding some warning when someone creates an index on
> an int2 column?
I don't think so. Better to expend our energy on solving the
fundamental problem.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
2003-08-26 04:17 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-26 13:09 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-26 23:32 ` Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 8+ messages in thread
From: Gaetano Mendola @ 2003-08-26 23:32 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Should we consider adding some warning when someone creates an index on
> > an int2 column?
>
> I don't think so. Better to expend our energy on solving the
> fundamental problem.
In the mean time that the fundamental problem is solved may be
a warning is usefull.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
2003-08-26 04:17 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-26 13:09 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-27 04:22 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-27 04:27 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2003-08-27 04:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marco Vezzoli <marco.vezzoli@st.com>; pgsql-sql
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Should we consider adding some warning when someone creates an index on
> > an int2 column?
>
> I don't think so. Better to expend our energy on solving the
> fundamental problem.
I am thinking _until_ we fix the problem. Is it actually hard to add?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres 7.1.3: why does the query plan ignore indexes?
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
2003-08-26 04:17 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-26 13:09 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Tom Lane <tgl@sss.pgh.pa.us>
2003-08-27 04:22 ` Re: postgres 7.1.3: why does the query plan ignore indexes? Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-27 04:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2003-08-27 04:27 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Marco Vezzoli <marco.vezzoli@st.com>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> Should we consider adding some warning when someone creates an index on
>>> an int2 column?
>>
>> I don't think so. Better to expend our energy on solving the
>> fundamental problem.
> I am thinking _until_ we fix the problem. Is it actually hard to add?
Why int2 in particular? And not int8? float4? Or numeric, float8,
or int4 in the wrong contexts? How about bpchar or varchar?
I figure we can waste at least as much time arguing about when/where/why
to issue warnings as it will take to solve the real problem.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-08-27 04:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-19 08:43 postgres 7.1.3: why does the query plan ignore indexes? Marco Vezzoli <marco.vezzoli@st.com>
2003-08-19 13:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-19 13:41 ` Marco Vezzoli <marco.vezzoli@st.com>
2003-08-26 04:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-26 13:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-26 23:32 ` Gaetano Mendola <mendola@bigfoot.com>
2003-08-27 04:22 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-27 04:27 ` Tom Lane <tgl@sss.pgh.pa.us>
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