Re: Query performance PLEASE HELP
Dmitry Tkach <dmitry@openratings.com> writes:
Explain analyze says:
Limit (cost=61.91..61.91 rows=1 width=192) (actual time=439435.47..439435.50 rows=10 loops=1)
-> Sort (cost=61.91..61.91 rows=1 width=192) (actual time=439435.47..439435.48 rows=11 loops=1)
-> Nested Loop (cost=0.00..61.90 rows=1 width=192) (actual time=7589.68..439423.75 rows=110 loops=1)
-> Index Scan using managed_supplier_idx on managed_supplier ms (cost=0.00..22.02 rows=5 width=157) (actual time=6.72..3009.90 rows=14365 loops=1)
-> Index Scan using tradestyle_duns_idx on tradestyle ts (cost=0.00..6.97 rows=1 width=35) (actual time=30.34..30.37 rows=0 loops=14365)
Total runtime: 439436.45 msec
Judging from the tiny cost estimates, the planner thinks these tables
are tiny. Have you done a VACUUM ANALYZE lately?
regards, tom lane
Import Notes
Reply to msg id not found: 3E3ACEE6.7060603@openratings.comReference msg id not found: 3E3ACEE6.7060603@openratings.com
Dmitry Tkach <dmitry@openratings.com> writes:
Well... Yes. I am doing that daily. Actually, I was wonderring about
those estimates too, but that's not my primary concern right now -
perhaps, it should be, but, as far as I understand, the estimate only
matter for the query plan selection, and I don't have a problem with the
query plan
You should. If the query can be done any faster, it will be by picking
a different query plan. I'm not sure what would be a better plan, but
certainly a large part of the problem is that the planner is so far off
about the rowcount estimates.
One thing I'm wondering is if the index on tradestyle.name could be helpful.
How selective is "ts.name like 'POST%'", exactly --- does that eliminate
a lot of rows, or not? Is the thing able to use that as an indexqual
(ie, are you in C locale)?
To tell you the truth, I do not believe your assertion that these tables
have been analyzed. I don't see how the rowcount estimates could be so
small if the planner were aware of the true table statistics. What does
pg_stats show for the columns used in the query?
regards, tom lane
Import Notes
Reply to msg id not found: 3E3AD78B.5050002@openratings.comReference msg id not found: 3E3ACEE6.7060603@openratings.com