[GENERAL] q: explain analyze
Hello, When I run 'explain analyze' on a query, how do I know what index is used and is it used at all. What are specific words should I look for? Is Seq Scan indicates that index has been used? How do I know that it was Full Table Scan? Thanks, Mark. __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] q: explain analyze
On 1/10/06, Mark [EMAIL PROTECTED] wrote: Hello, When I run 'explain analyze' on a query, how do I know what index is used and is it used at all. What are specific words should I look for? Is Seq Scan indicates that index has been used? How do I know that it was Full Table Scan? Thanks, Mark. Seq Scan is short for Sequential Scan (Full Table Scan)... you have to look for the word index to see what indexes are you using if any (the name of the indexes are used too, so if you now indexe's names you can find them in the explain analyze quickly) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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
Re: [GENERAL] q: explain analyze
This is great, Now here's my explain analyze: Seq Scan on balance (cost=0.00..54.51 rows=147 width=106) (actual time=0.026..0.767 rows=62 loops=1) Filter: (amount = 0::double precision) I do have an index on amount, but I guess it won't be used for = ... is there any way to force usage of index? another question: Can I defined index for _NOT_EQUAL_ ? I have a column that can have 5 values and my where is WHERE type 'A' OR type 'B' _or_ better to use: WHERE type ='C' OR type = 'D' OR type = 'E' Thank you, Mark. --- Jaime Casanova [EMAIL PROTECTED] wrote: On 1/10/06, Mark [EMAIL PROTECTED] wrote: Hello, When I run 'explain analyze' on a query, how do I know what index is used and is it used at all. What are specific words should I look for? Is Seq Scan indicates that index has been used? How do I know that it was Full Table Scan? Thanks, Mark. Seq Scan is short for Sequential Scan (Full Table Scan)... you have to look for the word index to see what indexes are you using if any (the name of the indexes are used too, so if you now indexe's names you can find them in the explain analyze quickly) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(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
Re: [GENERAL] q: explain analyze
Mark [EMAIL PROTECTED] writes: I do have an index on amount, but I guess it won't be used for = ... The general rule is that an index is only helpful for extracting a fairly small subset of the table (small can mean as little as 1%). So a one-sided inequality is not usefully indexable unless the comparison constant is near the end of the data range. The planner does understand this and will do the right things as long as the ANALYZE statistics are reasonably accurate. is there any way to force usage of index? You can try setting enable_seqscan = off, but you'll likely find that the planner is making the right decision. (If it isn't, you may want to play with the value of random_page_cost ... but be wary of changing that based on a small number of test cases.) Can I defined index for _NOT_EQUAL_ ? No. See above. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] q: explain analyze
On 1/10/06, Mark [EMAIL PROTECTED] wrote: This is great, Now here's my explain analyze: Seq Scan on balance (cost=0.00..54.51 rows=147 width=106) (actual time=0.026..0.767 rows=62 loops=1) Filter: (amount = 0::double precision) I do have an index on amount, but I guess it won't be used for = ... look at the rows field... the first one is the estimated by the planner the second is the actual number of rows retrieved for that Seq Scan... so if 147 (the estimated) is about a 10% of the total records in your table an index will not be used because it will be loss performance... is there any way to force usage of index? you can try SET enable_seqscan=off; before executing your query... that will increase the cost of a seq scan and not be used unless there is no other way to do it or the other methods are incredible slower another question: Can I defined index for _NOT_EQUAL_ ? I have a column that can have 5 values and my where is WHERE type 'A' OR type 'B' _or_ better to use: WHERE type ='C' OR type = 'D' OR type = 'E' is not a good idea if you only will have 5 different values... although you can create a partial index... but this is good only if you create for the value that will be appear less (maybe 10% of total record or less)... and can only be used for that specific case... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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
