Re: [HACKERS] How does the planner deal with multiple possible indexes?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > No, this is 8.1.3, and it's a production machine so I'd prefer not to go > about dropping indexes to get cost comparisons; unless there's some way > to disable the use of an index in a given backend? The traditional hack for that is begin; drop index foo; explain whatever; rollback; The DROP acquires an exclusive lock on the table, but it's only held for a very short time while you EXPLAIN (you might want to put the whole thing in a script file instead of relying on human typing speed). So unless you've got seriously strict response time requirements, this is generally OK even in production DBs. You do have to watch out for long running transactions holding non-exclusive locks, eg don't try this while a VACUUM is running on the table --- else the DROP blocks on the vacuum and all other accesses start to queue up behind the DROP. If the online-index-build patch gets in, there will be a cleaner option which is to just mark the index disabled in pg_index. That doesn't require any exclusive lock, indeed won't be visible to other backends at all if you do it within a transaction as above. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] How does the planner deal with multiple possible indexes?
On Wed, Jul 19, 2006 at 07:54:49PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Indeed, if I find a case where there's a large enough number of rows it > > will choose the smaller index. But I'm wondering if it would be better > > to always favor the smaller index, since it would (presumably) be easier > > to keep it in cache? > > AFAICS, in existing releases that should happen, because the cost > estimate varies with the size of the index. And it does happen for me > in simple tests. You did not provide the requested information to help > us find out why it's not happening for you. > > (I'm a bit worried about whether CVS HEAD may have broken this behavior > with the recent changes in the indexscan cost equations ... but unless > you are working with HEAD that's not relevant.) No, this is 8.1.3, and it's a production machine so I'd prefer not to go about dropping indexes to get cost comparisons; unless there's some way to disable the use of an index in a given backend? Otherwise I'll try and come up with a test case. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] How does the planner deal with multiple possible indexes?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Indeed, if I find a case where there's a large enough number of rows it > will choose the smaller index. But I'm wondering if it would be better > to always favor the smaller index, since it would (presumably) be easier > to keep it in cache? AFAICS, in existing releases that should happen, because the cost estimate varies with the size of the index. And it does happen for me in simple tests. You did not provide the requested information to help us find out why it's not happening for you. (I'm a bit worried about whether CVS HEAD may have broken this behavior with the recent changes in the indexscan cost equations ... but unless you are working with HEAD that's not relevant.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] How does the planner deal with multiple possible indexes?
On Wed, Jul 19, 2006 at 07:00:40PM -0400, Tom Lane wrote: > "Gregory Maxwell" <[EMAIL PROTECTED]> writes: > > On 7/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > [snip] > >> \d does list bdata__ident_filed_departure before bdata_ident; I'm > >> wondering if the planner is finding the first index with ident_id in it > >> and stopping there? > > >> From my own experience it was grabbing the first that has the > > requested field as its first member.. I haven't looked at the code to > > see if that is the intended behavior. > > Ordering would only matter if the estimated costs were exactly the same, > which they probably shouldn't be for indexes with such different sizes. > However, if the estimated number of matching rows were real small, you > might be winding up with a "one page to fetch" estimate in either case. > Jim didn't provide enough details to guess what the cost estimates > actually are... Indeed, if I find a case where there's a large enough number of rows it will choose the smaller index. But I'm wondering if it would be better to always favor the smaller index, since it would (presumably) be easier to keep it in cache? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] How does the planner deal with multiple possible indexes?
"Gregory Maxwell" <[EMAIL PROTECTED]> writes: > On 7/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > [snip] >> \d does list bdata__ident_filed_departure before bdata_ident; I'm >> wondering if the planner is finding the first index with ident_id in it >> and stopping there? >> From my own experience it was grabbing the first that has the > requested field as its first member.. I haven't looked at the code to > see if that is the intended behavior. Ordering would only matter if the estimated costs were exactly the same, which they probably shouldn't be for indexes with such different sizes. However, if the estimated number of matching rows were real small, you might be winding up with a "one page to fetch" estimate in either case. Jim didn't provide enough details to guess what the cost estimates actually are... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How does the planner deal with multiple possible indexes?
On 7/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: [snip] \d does list bdata__ident_filed_departure before bdata_ident; I'm wondering if the planner is finding the first index with ident_id in it and stopping there? From my own experience it was grabbing the first that has the requested field as its first member.. I haven't looked at the code to see if that is the intended behavior. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] How does the planner deal with multiple possible indexes?
Client has a table with 9 indexes; one is on just ident_id and takes up 75181 pages, the other is on ident_id and another field and uses 117461 pages. "bdata__ident_filed_departure" btree (ident_id, filed_departuretime), tablespace "array4" "bdata_ident" btree (ident_id), tablespace "array4" Whats interesting is that even a simple SELECT * FROM table WHERE ident_id=1234 uses bdata__ident_filled_departure, even though it would require less IO to use bdata_ident. \d does list bdata__ident_filed_departure before bdata_ident; I'm wondering if the planner is finding the first index with ident_id in it and stopping there? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
