January 9th, 2003, 02:28 AM
Indexing In Postgres
I'm having some very interesting problem over here.
I have a table that was indexed but there's not unique key in the table. When I tried to indexed it, it works but then when I set explain on the select statement, they won't make use of the index. Any idea what's happening ? or what is wrong with my indexing technique
Create table abc (
create index abc_name_idx on abc(name)
when i tried this :
explain select * from abc where name='john'
they will use a seq scan instead of an index scan
January 10th, 2003, 08:34 AM
How many rows in the table? If the table is small, postgres will NOT use any indexes since it's not really needed.
September 11th, 2003, 05:54 PM
I have the same problem
I have the same problem that rod k.
And I have 14.520.000 rows.
I dont know why postgres use Seq Scan when the index is there !
If someone could give me a clue, I will appreciate.
September 11th, 2003, 07:49 PM
Post the query, the explain output and the schema of all tables involved. Without that, we would just be speculating.
September 18th, 2003, 04:40 AM
I also have the same problem, with a "description" field of varchar(255).
I use LIKE for searching and takes something like 10 seconds (300.000 rows). It is a little bit for a query.
But I don't know what type of indexing should I use.
September 18th, 2003, 09:20 AM
what version of postgres are you guys using? also, you can use phppgadmin or if you're tricky and can do it with pg_dump you should see if the table structures reflect the index that you created. You could try to drop and then recreate the index as well. There are a few cases where postgres will not use an index, like rodk said if there are too few rows, but I think there is one other case??? something about hashes. I think you can build other types of indexes too, like R-tree index? I dunno but it's in the documentation.
Here are some tidbits:
While selecting 1000 out of 100000 rows could be a candidate for an index, selecting 1 out of 100 rows will hardly be, because the 100 rows will probably fit within a single disk page, and there is no plan that can beat sequentially fetching 1 disk page.
so, from this it seems that it depends on the ratio of records you're removing with your select.
When indexes are not used, it can be useful for testing to force their use. There are run-time parameters that can turn off various plan types (described in the Administrator's Guide). For instance, turning off sequential scans (enable_seqscan) and nested-loop joins (enable_nestloop), which are the most basic plans, will force the system to use a different plan. If the system still chooses a sequential scan or nested-loop join then there is probably a more fundamental problem for why the index is not used, for example, the query condition does not match the index. (What kind of query can use what kind of index is explained in the previous sections.)