Re: [PERFORM] Why isn't this index being used?
Hi, I ran into a similar problem using bigints... See: http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-INT small big int have to be cast when used in querries... try: explain select * from db where type=90::smallint and subtype=70::smallint and date='7/1/2004'; or explain select * from db where type='90' and subtype='70' and date='7/1/2004'; Knutsen, Mark wrote: The following is from a database of several hundred million rows of real data that has been VACUUM ANALYZEd. Why isn't the index being used for a query that seems tailor-made for it? The results (6,300 rows) take about ten minutes to retrieve with a sequential scan. A copy of this database with integer in place of smallint, a primary key in column order (date, time, type, subtype) and a secondary index in the required order (type, subtype, date, time) correctly uses the secondary index to return results in under a second. Actually, the integer version is the first one I made, and the smallint is the copy, but that shouldn't matter. Postgres is version postgresql-server-7.3.4-3.rhl9 from Red Hat Linux 9. = testdb2=# \d db Table public.db Column | Type | Modifiers -++--- date| date | not null time| time without time zone | not null type| smallint | not null subtype | smallint | not null value | integer| Indexes: db_pkey primary key btree (type, subtype, date, time) testdb2=# set enable_seqscan to off; SET testdb2=# explain select * from db where type=90 and subtype=70 and date='7/1/2004'; QUERY PLAN -- Seq Scan on db (cost=1.00..107455603.76 rows=178 width=20) Filter: ((type = 90) AND (subtype = 70) AND (date = '2004-07-01'::date)) (2 rows) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why isn't this index being used?
(Why don't replies automatically go to the list?) Sure enough, quoting the constants fixes the problem. Is it a best practice to always quote constants? -Original Message- From: Doug Y [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 11:28 AM To: Knutsen, Mark Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Why isn't this index being used? Hi, I ran into a similar problem using bigints... See: http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-INT small big int have to be cast when used in querries... try: explain select * from db where type=90::smallint and subtype=70::smallint and date='7/1/2004'; or explain select * from db where type='90' and subtype='70' and date='7/1/2004'; Knutsen, Mark wrote: The following is from a database of several hundred million rows of real data that has been VACUUM ANALYZEd. Why isn't the index being used for a query that seems tailor-made for it? The results (6,300 rows) take about ten minutes to retrieve with a sequential scan. A copy of this database with integer in place of smallint, a primary key in column order (date, time, type, subtype) and a secondary index in the required order (type, subtype, date, time) correctly uses the secondary index to return results in under a second. Actually, the integer version is the first one I made, and the smallint is the copy, but that shouldn't matter. Postgres is version postgresql-server-7.3.4-3.rhl9 from Red Hat Linux 9. = testdb2=# \d db Table public.db Column | Type | Modifiers -++--- date| date | not null time| time without time zone | not null type| smallint | not null subtype | smallint | not null value | integer| Indexes: db_pkey primary key btree (type, subtype, date, time) testdb2=# set enable_seqscan to off; SET testdb2=# explain select * from db where type=90 and subtype=70 and date='7/1/2004'; QUERY PLAN -- Seq Scan on db (cost=1.00..107455603.76 rows=178 width=20) Filter: ((type = 90) AND (subtype = 70) AND (date = '2004-07-01'::date)) (2 rows) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Why isn't this index being used?
On Tue, Oct 19, 2004 at 11:33:50AM -0400, Knutsen, Mark wrote: (Why don't replies automatically go to the list?) Because sometimes you don't want them to. There's been dozens of discussions about this. BTW, mutt has a nice feature which allows you to reply to lists -- I imagine other MUAs have such a feature too. Sure enough, quoting the constants fixes the problem. Is it a best practice to always quote constants? No, but it's very useful in these cases. The problem is I believe this is fixed in 8.0, BTW. See the FAQ, question 4.8 A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
