Very disappointing performance -- All Indexes Ignored. - Mailing list pgsql-sql

    I'm experiencing very disappointing performance from PostgreSQL, and
yes, I've done  VACUUM ANALYZE(every night)... It completely ignores my
indexes on simple equijoins, at 2 or 3 tables it isn't a big deal, at 7
it takes 5 minutes...  I verified this by blowing away all my indexes
and re-running my queries, there was NO PERFORMANCE CHANGE... Is this a
bug?  The tables are 10,000 & 20,000 touples...  Here are the queries:

-- !       343.585766 elapsed 333.160000 user 0.790000 system sec
-- !       332.302620 elapsed 330.700000 user 0.650000 system sec
-- !       333.938455 elapsed 331.520000 user 0.780000 system sec
-- NOINDEX: !       302.483055 elapsed 301.030000 user 0.450000 system
sec
select  po_id,
        ticket_pk,
        material.name,
        vendor.name,
        cstcode_name(code_id),
        units.name,
        potype.name

FROM po,tickets,material,vendor,units,potype

WHERE   po_id=material_po AND
        po.units_id=tickets.units_id AND
        po.material_id=material.material_id AND
        po.vendor_id=vendor.vendor_id AND
        po.units_id=units.units_id AND
        po.potype_id=potype.potype_id

    There are BTREE indexes on every single thing I'm joining there...
This query takes a disappointing 5 minutes to run, here is the EXPLAIN:

NOTICE:  QUERY PLAN:

Hash Join  (cost=164074.25 size=1027081848 width=98)
  ->  Hash Join  (cost=5491.01 size=4120654 width=88)
        ->  Hash Join  (cost=1650.02 size=115634 width=72)
              ->  Hash Join  (cost=1228.24 size=12450 width=56)
                    ->  Nested Loop  (cost=866.49 size=10867 width=42)
                          ->  Seq Scan on units  (cost=1.13 size=4
width=14)
                          ->  Index Scan using ipo_units_id on po
(cost=216.34
size=10867 width=28)
                    ->  Hash  (cost=0.00 size=0 width=0)
                          ->  Seq Scan on potype  (cost=1.07 size=2
width=14)
              ->  Hash  (cost=0.00 size=0 width=0)
                    ->  Seq Scan on material  (cost=5.47 size=105
width=16)
        ->  Hash  (cost=0.00 size=0 width=0)
              ->  Seq Scan on vendor  (cost=13.54 size=289 width=16)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Seq Scan on tickets  (cost=906.93 size=20513 width=10)

    The EXPLAIN after dropping all indexes is exactly the same... Here
is a far faster query I've come up with, but I would think it should be
slower:

-- !       31.190193 elapsed 30.090000 user 0.880000 system sec

select  po_id,
        ticket_pk,
        material.name,
        vendor.name,
        unit_name(po.units_id),
        potype_name(po.potype_id),
        cstcode_name(code_id)

FROM po,tickets,material,vendor

WHERE   po_id=material_po AND
        po.units_id=tickets.units_id AND
        po.material_id=material.material_id AND
        po.vendor_id=vendor.vendor_id

    The functions are a simple lookup on tables of about 300 touples...
This should be a pathetically slow query but it takes 30 seconds vs 300!
... I think the PostgreSQL optimizer is just failing miserably on my
schema, and I can't figure out why...

    If there is someplace I can call for help, or pay for help that's
fine, I don't mind paying for support, but this data will only grow, and
the reporting time will grow from 5 minutes... Not taking advantage of
indexes on a simple equijoin(I also tested a simple equijoin and it
displayed the same problems) is bad, not taking advantage of them when
joining 5+ tables is a recipe for disaster.... I could switch to mySQL
but I hate that views are not offered, and that security isn't as
good.(but I know it will use my indexes)

    Should I report this as a bug?  Anyone have some suggestions?  I'd
be glad to give developers access to a copy of the database to see the
problem first hand(with the prices & such removed from the tables that
matter)...

    Is there any place that I can pay for support on this product?

David Secret
MIS Director
Kearney Development Co., Inc.