Обсуждение: optimizer not using an index...
SELECT customers.usedby, customers.hostname, customers.email, customers.valid, customers.signuptime, pincodes.codenum, pincodes.code, subaccts.type, subaccts.batch, subaccts.renew, subaccts.active, subaccts.price, subaccts.rebill, domains.name, domains.client, domains.authtype, ibill.login, ibill.passwd, customers.transnum, customers.subscr FROM pincodes, subaccts, customers, domains, owners, ibill WHERE pincodes.codenum=customers.codenum AND pincodes.type=subaccts.type AND domains.client=subaccts.clientAND domains.client=owners.client AND ibill.client=domains.client AND customers.email='caffeine@toodarkpark.org'; explain reveals that postgres ( 6.5.0 ) isnt using some of my indexes, opting instead for a complete table scan ( and drastically slowing things down ). Hash Join (cost=48272.95 rows=293465 width=222) -> Nested Loop (cost=20598.72 rows=376206 width=138) -> Hash Join (cost=58.73 rows=413 width=118) -> Seq Scan on subaccts (cost=17.51 rows=379 width=58) -> Hash (cost=24.84 rows=87 width=60) -> Hash Join (cost=24.84 rows=87 width=60) -> Hash Join (cost=14.56 rows=81 width=56) -> Seq Scan on ibill (cost=3.64 rows=80width=28) -> Hash (cost=4.64 rows=80 width=28) -> Seq Scan on domains (cost=4.64 rows=80 width=28) -> Hash (cost=3.81 rows=85 width=4) -> Seq Scan on owners (cost=3.81 rows=85 width=4) -> Index Scan using codes_type_idx onpincodes (cost=49.73 rows=345235 width=20) -> Hash (cost=546.46 rows=8757 width=84) -> Seq Scan on customers (cost=546.46 rows=8757 width=84) I have an index on customers.name, subaccts.type, ibill.client, owners.client... every column thats being queried on. tables are all vacuum analyze'ed ( the DEBUG notice shows that postgres does indeed 'see' the indexes, which are all btree's, btw ). customers table has 12k entries, pincodes has 350k, ibill as 80, domains has 80, owners has 80, subaccts has 380. doing a complete table scan on a column thats indexed isnt really that nice, especially since there are 12,000 entries in it. why postgres chooses to use table scans on other tables is also beyond me: "explain select * from (ibill|domains|owners|subaccts) where client=1" uses the proper index. the hash join and nested loop also bug me; thats a lot of rows to cycle through. interestingly, when querying on "pincodes.code" instead of "customers.name", postgres does NOT use a full table scan; it uses the proper indexes: Hash Join (cost=23.78 rows=5 width=222) -> Seq Scan on ibill (cost=3.64 rows=80 width=28) -> Hash (cost=16.37 rows=4width=194) -> Nested Loop (cost=16.37 rows=4 width=194) -> Nested Loop (cost=10.22 rows=3 width=190) -> Nested Loop (cost=6.12 rows=2 width=162) -> Nested Loop (cost=4.07rows=1 width=104) -> Index Scan using codes_code_idx on pincodes (cost=2.02 rows=1width=20) -> Index Scan using users_pkey on customers (cost=2.05 rows=11226 width=84) -> Index Scan using types_pkey on subaccts (cost=2.05 rows=379 width=58) -> Index Scan using doms_pkey on domains (cost=2.05 rows=80 width=28) -> Index Scan using owner_client_idxon owners (cost=2.05 rows=85 width=4) so what gives ? the two queries are 90% identical apart from the column that's being keyed on ( customers.name -vs- pincodes.code ). any help would be MOST appreciated. --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "The distance between insanity and genius is measured only by success."
Howie <caffeine@toodarkpark.org> writes: > explain reveals that postgres ( 6.5.0 ) isnt using some of my indexes, > opting instead for a complete table scan ( and drastically slowing things > down ). Well, mumble. The optimizer certainly needs work, but what's your evidence that this query would be faster if done another way? Hashing the smaller tables, as it's doing, ought to be a pretty good strategy. One way to check is to start your client with environment variable setting PGOPTIONS="-fh" ("forbid hashjoin") to discourage the optimizer from using hashes, then check the generated plan for the same query and see what its actual runtime is. That's likely to be a suboptimal plan however, since it'll turn off *all* hashing. The hash on customers is probably the thing that's bothering you. How many result rows do you actually get from this query? If you eliminate the customers table from the query, and just do the same join among the remaining tables, how many rows do you get? I suspect the optimizer is drastically off in its estimate of ~300k result rows, and that's contributing to the problem. > doing a complete table scan on a column thats indexed isnt really that > nice, especially since there are 12,000 entries in it. But it's estimating it's going to have to probe that table 300k times, which makes the hashjoin look mighty attractive... > interestingly, when querying on "pincodes.code" instead of > "customers.name", postgres does NOT use a full table scan; it uses the > proper indexes: > Hash Join (cost=23.78 rows=5 width=222) Note the drastic difference in the estimated result-row count; that's undoubtedly what's changing the optimizer's choice of what to do. You haven't given us enough detail to understand why this query would be (or at least seem) more selective than the other, however. Anyway, this looks to me like it is probably a symptom of poor selectivity estimation leading to bogus estimates of output row counts leading to a nonoptimal plan choice. I have been working on improving the selectivity estimation for 6.6, and am looking for test cases to check out the logic on. Is your database small enough/non proprietary enough that you could send me a dump? Or could you strip it down to a test case that still exhibits the same misbehavior? If you don't like either of those, perhaps you could grab a current snapshot, install your data in a test postmaster, and report back on whether it acts any different... regards, tom lane
On Fri, 27 Aug 1999, Tom Lane wrote: > Howie <caffeine@toodarkpark.org> writes: > > explain reveals that postgres ( 6.5.0 ) isnt using some of my indexes, > > opting instead for a complete table scan ( and drastically slowing things > > down ). > > Well, mumble. The optimizer certainly needs work, but what's your > evidence that this query would be faster if done another way? Hashing > the smaller tables, as it's doing, ought to be a pretty good strategy. primary focus was on why pgsql decided to do a complete table scan of customers ( searching for customer.email ) when there's an index on it; surely an index scan would be loads faster than a complete table scan. the other interesting item i ran into was why one query used the indexes provided and the other didnt. granted, pincodes has at least 10x more entries than customers does ( ~300k+ vs ~11k ). > One way to check is to start your client with environment variable > setting PGOPTIONS="-fh" ("forbid hashjoin") to discourage the optimizer > from using hashes, then check the generated plan for the same query and > see what its actual runtime is. That's likely to be a suboptimal plan > however, since it'll turn off *all* hashing. The hash on customers is > probably the thing that's bothering you. that and pgsql not using the indexes that the query on pincodes.code does. using -fh causes pgsql to use all the proper indexes, but still beefs up 4 merge joins, seq scans, sorts, and nested loops: [excerpt] Merge Join (cost=355979.91 rows=293465 width=222) -> Seq Scan (cost=342501.81 rows=376206 width=138) -> Sort (cost=342501.81rows=376206 width=138) -> Nested Loop (cost=20616.32 rows=376206 width=138) -> Merge Join (cost=76.34 rows=413 width=118) -> Merge Join (cost=33.01 rows=87 width=60) -> Merge Join (cost=20.28 rows=81 width=56) ( query on customers.email ) > How many result rows do you actually get from this query? when querying on pincodes.code, i get 1 row back. when querying on customers.email, i get 4 rows back. it's producing the correct results, its just going about getting those results in a somewhat odd manner. > If you eliminate the customers table from the query, and just do the > same join among the remaining tables, how many rows do you get? 1, as expected. Hash Join (cost=21.73 rows=5 width=138) -> Seq Scan on ibill (cost=3.64 rows=80 width=28) -> Hash (cost=14.32 rows=4width=110) -> Nested Loop (cost=14.32 rows=4 width=110) -> Nested Loop (cost=8.17 rows=3 width=106) -> Nested Loop (cost=4.07 rows=2 width=78) -> Index Scan using codes_code_idxon pincodes (cost=2.02 rows=1 width=20) -> Index Scan using types_pkey on subaccts(cost=2.05 rows=379 width=58) -> Index Scan using doms_pkey on domains (cost=2.05 rows=80 width=28) -> Index Scan using owner_client_idx on owners (cost=2.05 rows=85 width=4) > I suspect the optimizer is drastically off in its estimate of ~300k > result rows, and that's contributing to the problem. yes, so why is it off for one query but right on target for the other ? more importantly, why is it chosing to use indexes for one query yet chosing to do complete table scans for the other ( even though the two queries are almost identical ) ? answer that and i'll personally treat you to a beer. :) > > doing a complete table scan on a column thats indexed isnt really that > > nice, especially since there are 12,000 entries in it. > > But it's estimating it's going to have to probe that table 300k times, > which makes the hashjoin look mighty attractive... why would it have to probe pincodes 300k times when there's a unique index on pincodes ( pincodes.codenum ) and a unique index on customers ( customers.codenum ) ? > > interestingly, when querying on "pincodes.code" instead of > > "customers.name", postgres does NOT use a full table scan; it uses the > > proper indexes: > > > Hash Join (cost=23.78 rows=5 width=222) > > Note the drastic difference in the estimated result-row count; that's > undoubtedly what's changing the optimizer's choice of what to do. You > haven't given us enough detail to understand why this query would be > (or at least seem) more selective than the other, however. im not sure it would; i placed indexes in ( what i thought were ) all the proper places - pincodes.code, pincodes.codenum, customers.name, customers.codenum... every column that's being queried on or joined on. ideally, all the indexes would be used and querying on customers.email would be ( query-plan wise ) almost identical to querying on pincodes.code; only an index would change. > Anyway, this looks to me like it is probably a symptom of poor > selectivity estimation leading to bogus estimates of output row counts > leading to a nonoptimal plan choice. I have been working on improving > the selectivity estimation for 6.6, and am looking for test cases to > check out the logic on. Is your database small enough/non proprietary > enough that you could send me a dump? i could send you a dump, but the db is fairly large; 7m uncompressed, 2m gzip -9'ed. none of the data is overly sensetive. i am, however, on an overly lagged 28k8. > Or could you strip it down to > a test case that still exhibits the same misbehavior? i havent tried trimming the db down. im not sure that would 'fix' the problem if the optimizer is misguessing the number of rows it's going to have to look at... > If you don't > like either of those, perhaps you could grab a current snapshot, install > your data in a test postmaster, and report back on whether it acts any > different... over the weekend im planning on upgrading to 6.5.1, but i dont recall seeing any changes to the optimizer in the changelog... --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "The distance between insanity and genius is measured only by success."
Howie <caffeine@toodarkpark.org> writes: > On Fri, 27 Aug 1999, Tom Lane wrote: >> Well, mumble. The optimizer certainly needs work, but what's your >> evidence that this query would be faster if done another way? Hashing >> the smaller tables, as it's doing, ought to be a pretty good strategy. > primary focus was on why pgsql decided to do a complete table scan of > customers ( searching for customer.email ) when there's an index on it; > surely an index scan would be loads faster than a complete table scan. No, an index scan will be loads slower if most or all of the table has to be visited. If it were always faster, the optimizer would have a much easier task ;-). The join we are considering here is on pincodes.codenum = customers.codenum, and since the system (mistakenly) thinks it is going to be joining a lot of rows from pincodes, doing it with a hash rather than an indexscan on customers.codenum looks good. You bring up a good point though: why isn't it deciding to use the restriction on customer.email for an indexscan of customers, using the index on email rather than the one on codenum? (I assume you have both) It looks like the thing is misestimating the selectivity of that restriction too: there are not going to be 8757 customer rows that pass that WHERE clause --- but the system thinks that, and that's why it's rejecting the indexscan as being slower than the sequential. I had missed that point before, being distracted by the even sillier row count from the other subjoin. There are clearly two different selectivity mistakes being made here :-( > the other interesting item i ran into was why one query used the indexes > provided and the other didnt. granted, pincodes has at least 10x more > entries than customers does ( ~300k+ vs ~11k ). Right, and the issue seems to be that when you are restricting pincodes with an extra WHERE clause, the estimated output row count is realistic, whereas when you aren't restricting pincodes it ain't. I'm not sure why, yet. >> But it's estimating it's going to have to probe that table 300k times, >> which makes the hashjoin look mighty attractive... > why would it have to probe pincodes 300k times when there's a unique index > on pincodes ( pincodes.codenum ) and a unique index on customers ( > customers.codenum ) ? It's guessing that it's going to get 300k rows out of the join of pincodes to all the little tables (ie, about one row per pincode row; it doesn't know there's only going to be one match), and then it thinks it's going to have to look through most of the customers table for match(es) to each of those rows. If the selectivity estimates that the system was making were in the right ballpark, then the hash join probably *would* be the right way to do it. The problem here seems to be that the estimated row counts are so far off-base. I'd like to find out why. > i could send you a dump, but the db is fairly large; 7m uncompressed, 2m > gzip -9'ed. none of the data is overly sensetive. i am, however, on an > overly lagged 28k8. 2m gzipped is no problem at this end, but I'll bet we'd see the same behavior with only half or a third of the data, if you can extract a consistent subset easily... > i havent tried trimming the db down. im not sure that would 'fix' the > problem if the optimizer is misguessing the number of rows it's going to > have to look at... No, it wouldn't be a fix for your problem. I was just thinking of making a smaller, easier-to-ship test case that I could use to make sure the problem is fixed for 6.6. > over the weekend im planning on upgrading to 6.5.1, but i dont recall > seeing any changes to the optimizer in the changelog... There are not; this is work for 6.6. The fixes involve storing more and different info in the system statistics table, so there is no prospect of back-patching them into 6.5.*. (BTW, I think 6.5.2 will be out shortly, so you might want to wait another week before updating.) regards, tom lane