Thread: Very disappointing performance -- All Indexes Ignored.
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.
OK, some questions. First, how long does EXPLAIN take to run. It is a significant percentage of the query time? If so, please download the current snapshot, and try that. Should be much faster. Second, indexes are not of a huge value in this query, because there are no restrictions, i.e. x=3. In most cases, walking the index and then going to the heap table takes longer than just reading the entire table into a hash and doing the join there. I am interested in seeing this resolved for you. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote:
> OK, some questions. First, how long does EXPLAIN take to run. It is a
> significant percentage of the query time? If so, please download the
> current snapshot, and try that. Should be much faster.
>
Not very long, maybe 10 seconds at maximum... I checked this because I
was
paranoid it might be that... No problem there.
>
> Second, indexes are not of a huge value in this query, because there are
> no restrictions, i.e. x=3. In most cases, walking the index and then
> going to the heap table takes longer than just reading the entire table
> into a hash and doing the join there.
I see, and after doing a more specific query(from_job_id=xxx) the speed
is much increased... The main thing that disturbed me is the rather large
time between the query with joins(300 seconds) and with the functions(30s)...
Shouldn't the functions be far slower?(Seems like they would be less
efficient)
PostgreSQL is also crashing 1-2 times a day on me, although I have a
handy perl script to keep it alive now <grin>... If PostgreSQL does work for
my project I'll of course buy CDs/books/whatever to support the project.
Despite the 2 problems I'm having I am very impressed with the project.
--David
> > Second, indexes are not of a huge value in this query, because there are > > no restrictions, i.e. x=3. In most cases, walking the index and then > > going to the heap table takes longer than just reading the entire table > > into a hash and doing the join there. > > I see, and after doing a more specific query(from_job_id=xxx) the speed > is much increased... The main thing that disturbed me is the rather large > time between the query with joins(300 seconds) and with the functions(30s)... > Shouldn't the functions be far slower?(Seems like they would be less > efficient) Well, you are doing the functions only for matched rows, while the other does full-table joins. Can you easily test the snapshot and let me know? I have re-done the optimizer for not-yet-released 6.5, and that may fix the problem. I fixed many optimizer problems, and it may be choosing a non-optimal join path. > > PostgreSQL is also crashing 1-2 times a day on me, although I have a > handy perl script to keep it alive now <grin>... If PostgreSQL does work for > my project I'll of course buy CDs/books/whatever to support the project. > Despite the 2 problems I'm having I am very impressed with the project. That is strange. Don't here that much. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
secret <secret@kearneydev.com> writes:
> PostgreSQL is also crashing 1-2 times a day on me, although I have a
> handy perl script to keep it alive now <grin>...
Details please? 6.4.x has been pretty stable for me, and for most other
people as far as I've heard. If you're getting a backend coredump,
a stack backtrace with gdb or equivalent would be helpful info.
regards, tom lane
Tom Lane wrote:
> secret <secret@kearneydev.com> writes:
> > PostgreSQL is also crashing 1-2 times a day on me, although I have a
> > handy perl script to keep it alive now <grin>...
>
> Details please? 6.4.x has been pretty stable for me, and for most other
> people as far as I've heard. If you're getting a backend coredump,
> a stack backtrace with gdb or equivalent would be helpful info.
>
> regards, tom lane
I sent in a bug report on it. Would a -d x report be helpful? I included
a -d 3 but if a higher level would be better I'll surely include that,
basically the server randomly dies with a:
ERROR: postmaster: StreamConnection: accept: Invalid argument
pmdie 3
(then signals all children to drop dead)
And all the postmasters go ZOMBIE. It doesn't core as far as I can tell,
it just
dies at the invalid argument and brings everything crashing down behind it.
Could the ODBC driver be sending something goofy that makes it coke? It's
very irregular and I haven't been able to get an ODBC trace yet.
I did however find a fun new CREATE VIEW which crashes the backend 100%,
it's on the bugs list with a 'method of reproduction' that worked for me 100%,
I think it'll probably be an easy fix.(It doesn't zombie all the postmasters
like the other one tho)
--David