Re: Postgres 9.0 has a bias against indexes - Mailing list pgsql-performance

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@vmsinfo.com> 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@postgresql.org)
> 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.