Odds are that a table of 14 rows will more likely be cached in RAM than a table of 14 million rows. PostgreSQL would certainly be more "openminded" to using an index if chances are low that the table is cached. If the table *is* cached, though, what point would there be in reading an index? Also, if random_page_cost is set to default (4.0), the planner will tend towards sequential scans. You can drop this number a bit to "help" the planner be more selective of indexes...and there's also cpu_tuple_* settings that can be modified to pursuade the planner to use indexes. Doubtful that any prodding will force an index scan with a cached table of 14 rows, though... On 1/27/11, Mladen Gogala <mladen.gogala@xxxxxxxxxxx> wrote: > I have a table EMP, with 14 rows and a description like this: > scott=> \d+ emp > Table "public.emp" > Column | Type | Modifiers | Storage | > Description > ----------+-----------------------------+-----------+----------+------------- > empno | smallint | not null | plain | > ename | character varying(10) | | extended | > job | character varying(9) | | extended | > mgr | smallint | | plain | > hiredate | timestamp without time zone | | plain | > sal | double precision | | plain | > comm | double precision | | plain | > deptno | smallint | | plain | > Indexes: > "emp_pkey" PRIMARY KEY, btree (empno) > "emp_mgr_i" btree (mgr) > Foreign-key constraints: > "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno) > Has OIDs: no > > scott=> > > A recursive query doesn't use existing index on mgr: > scott=> explain analyze > with recursive e(empno,ename,mgr,bossname,level) as ( > select empno,ename,mgr,NULL::varchar,0 from emp where empno=7839 > union > select emp.empno,emp.ename,emp.mgr,e.ename,e.level+1 > from emp,e > where emp.mgr=e.empno) > select * from e; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------- > CTE Scan on e (cost=20.59..23.21 rows=131 width=78) (actual > time=0.020..0.143 rows=14 loops=1) > CTE e > -> Recursive Union (cost=0.00..20.59 rows=131 width=52) (actual > time=0.018..0.128 rows=14 loops=1) > -> Seq Scan on emp (cost=0.00..1.18 rows=1 width=10) > (actual time=0.013..0.015 rows=1 loops=1) > Filter: (empno = 7839) > -> Hash Join (cost=0.33..1.68 rows=13 width=52) (actual > time=0.016..0.021 rows=3 loops=4) > Hash Cond: (public.emp.mgr = e.empno) > -> Seq Scan on emp (cost=0.00..1.14 rows=14 > width=10) (actual time=0.001..0.004 rows=14 loops=4) > -> Hash (cost=0.20..0.20 rows=10 width=44) (actual > time=0.004..0.004 rows=4 loops=4) > Buckets: 1024 Batches: 1 Memory Usage: 1kB > -> WorkTable Scan on e (cost=0.00..0.20 > rows=10 width=44) (actual time=0.001..0.002 rows=4 loops=4) > Total runtime: 0.218 ms > (12 rows) > > scott=> > > The optimizer will not use index, not even when I turn off both hash and > merge joins. This is not particularly important for a table with 14 > rows, but for a larger table, this is a problem. The > only way to actually force the use of index is by disabling seqscan, but > that chooses a wrong path > again, because it reads the "outer" table by primary key, which will be > very slow. Full table scan, > done by the primary key is probably the slowest thing around. I know > about the PostgreSQL philosophy > which says "hints are bad", and I deeply disagree with it, but would it > be possible to have at > least one parameter that would change calculations in such a way that > indexes are favored, where they exist? > > -- > Mladen Gogala > Sr. Oracle DBA > 1500 Broadway > New York, NY 10036 > (212) 329-5251 > www.vmsinfo.com > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Computers are like air conditioners... They quit working when you open Windows. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
