Обсуждение: Unused Indexes
Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim
Tim McAuley wrote: > Hi, > > I have a table which I have populated with over 5000 entries. There is a > combined index placed on two of the columns (both bigint). I am trying > a simple select (i.e. select id where col1 = 1 and col2 = 1) covering > these two columns and it keeps using a seq scan. Is this correct? I > would have thought that with this number of entries that an index scan > should be used. You must cast the 1 to a bigint: SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint This should probably be listed under FAQ 4.8, but it isn't. Hope that helps, Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > Tim McAuley wrote: > >>Hi, >> >>I have a table which I have populated with over 5000 entries. There is a >>combined index placed on two of the columns (both bigint). I am trying >>a simple select (i.e. select id where col1 = 1 and col2 = 1) covering >>these two columns and it keeps using a seq scan. Is this correct? I >>would have thought that with this number of entries that an index scan >>should be used. > > > You must cast the 1 to a bigint: > > SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint That should read: SELECT id WHERE col1 = 1::bigint AND col2 = 1::bigint. My dyslexia is kicking in... Mike Mascari mascarm@mascari.com
Assuming you have done a 'VACUUM ANALYZE' on the table in question you are most likely running into a type coercion issue. So explicitly cast your constants to bigint and the index should start being considered. select id from <table> where col2 = 1::bigint and col2 = 1::bigint Tim McAuley wrote: > Hi, > > I have a table which I have populated with over 5000 entries. There is > a combined index placed on two of the columns (both bigint). I am > trying a simple select (i.e. select id where col1 = 1 and col2 = 1) > covering these two columns and it keeps using a seq scan. Is this > correct? I would have thought that with this number of entries that an > index scan should be used. > > I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. > > Doing "set enable_seqscan to off" does not change the results of the > explain operation. > > I also tried setting a single index on just one of the columns and > running an appropriate search; it still uses a seq scan. At what stage > will the planner normally start using an index scan? > > Any hints appreciated. > > Tim > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
You need to convert the int's to bigints. select id where col1 = 1::bigint and col2 = 1::bigint Regards, Ed -----Original Message----- From: Tim McAuley <mcauleyt@tcd.ie> Date: Wed, 30 Jul 2003 13:46:46 To:pgsql-general@postgresql.org Subject: [GENERAL] Unused Indexes Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Mike Mascari wrote: >Tim McAuley wrote: > > >>Hi, >> >>I have a table which I have populated with over 5000 entries. There is a >>combined index placed on two of the columns (both bigint). I am trying >>a simple select (i.e. select id where col1 = 1 and col2 = 1) covering >>these two columns and it keeps using a seq scan. Is this correct? I >>would have thought that with this number of entries that an index scan >>should be used. >> >> > >You must cast the 1 to a bigint: > >SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint > >This should probably be listed under FAQ 4.8, but it isn't. > > That's it! I had actually just come across that before reading this email. It gets the explain back into shape anyway! Thanks! Tim