Re: [PERFORM] Index ot being used
On Mon, 2005-06-13 at 17:30 -0400, Madison Kelly wrote:
As I mentioned to Bruno in my reply to him, I am trying to keep as
many tweaks as I can inside my program. The reason for this is that this
is a backup program that I am trying to aim to more mainstream users or
where a techy would set it up and then it would be used by mainstream
users. At this point I want to avoid, as best I can, any changes from
default to the 'postgres.conf' file or other external files. Later
though, once I finish this testing phase, I plan to write a section of
external tweaking where I will test these changes out and note my
success for mre advanced users who feel more comfortable playing with
postgres (and web server, rsync, etc) configs.
If there is any way that I can make changes like this similar from
inside my (perl) program I would prefer that. For example, I implemented
the 'enable_seqscan' via:
$DB-do(SET ENABLE_SEQSCAN TO OFF) || die...
...
$DB-do(SET ENABLE_SEQSCAN TO ON) || die...
Your goal is admirable. However, many people tweak their postgresql.conf
files, and your program can't know whether or not this has happened. It
might be a good idea to have a var $do_db_optimization, which defaults
to on. Then, if your users have trouble or are advanced admins they can
turn it off. My personal opinion is that there are too many
architectures and configurations for you to accurately optimize inside
your program, and this gives you and your users an easy out.
if ($do_db_optimization == 1) {
$DB-do(SET ENABLE_SEQSCAN TO OFF) || die...
} else {
# do nothing -- postgresql will figure it out
}
--
Karim Nassar [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Index ot being used
Karim Nassar wrote:
Your goal is admirable. However, many people tweak their postgresql.conf
files, and your program can't know whether or not this has happened. It
might be a good idea to have a var $do_db_optimization, which defaults
to on. Then, if your users have trouble or are advanced admins they can
turn it off. My personal opinion is that there are too many
architectures and configurations for you to accurately optimize inside
your program, and this gives you and your users an easy out.
if ($do_db_optimization == 1) {
$DB-do(SET ENABLE_SEQSCAN TO OFF) || die...
} else {
# do nothing -- postgresql will figure it out
}
That is a wonderful idea and I already have the foundation in place to
easily implement this. Thanks!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 3: 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: [PERFORM] Index ot being used
On Mon, Jun 13, 2005 at 00:29:08 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: On Sun, Jun 12, 2005 at 23:42:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: As you probably saw in my last reply, I went back to the old index and tried the query you and Tom Lane recommended. Should this not have caught the index? Probably, but there might be some other reason the planner thought it was better to not use it. Using indexes is not always faster. It would help to see your latest definition of the table and indexes, the exact query you used and explain analyze output. Okay, here's what I have at the moment: tle-bu= \d file_info_7 Table public.file_info_7 Column| Type |Modifiers --+--+- file_group_name | text | file_group_uid | bigint | not null file_mod_time| bigint | not null file_name| text | not null file_parent_dir | text | not null file_perm| text | not null file_size| bigint | not null file_type| character varying(2) | not null default 'f'::character varying file_user_name | text | file_user_uid| bigint | not null file_backup | boolean | not null default true file_display | boolean | not null default false file_restore_display | boolean | not null default false file_restore | boolean | not null default false Indexes: file_info_7_display_idx btree (file_parent_dir, file_name) tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=15091.53..15165.29 rows=29502 width=114) (actual time=12834.933..12955.136 rows=25795 loops=1) Sort Key: file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11762.44 rows=29502 width=114) (actual time=0.244..2533.388 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 13042.421 ms (5 rows) Since my last post I went back to a query closer to what I actually want. What is most important to me is that 'file_parent_dir, file_name, file_display' are returned and that the results are sorted by 'file_parent_dir, file_name' and the results are restricted to where 'file_info='d''. I am guessing you mean 'file_type' instead of 'file_info'. To do this efficiently you want an index on (file_type, file_parent_dir, file_name). Currently you only have an index on (file_parent_dir, file_name) which won't help for this query. You also need to order by file_type even though it will be constant for all of the returned rows in order to help out the planner. This will allow an index scan over the desired rows that returns them in the desired order. Please actually try this before changing anything else. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Index ot being used
It sure would be nice if the optimizer would consider that it had the leeway to add any column which was restricted to a single value to any point in the ORDER BY clause. Without that, the application programmer has to know what indexes are on the table, rather than being able to just worry about the set of data they want. Obviously, if a column can have only one value in the result set, adding to any point in the ORDER BY can't change anything but performance. That sure sounds like something which should fall within the scope of an optimizer. It really should be a DBA function to add or drop indexes to tune the performance of queries, without requiring application programmers to modify the queries for every DBA adjustment. (When you have a database with over 350 tables and thousands of queries, you really begin to appreciate the importance of this.) Tom Lane [EMAIL PROTECTED] 06/12/05 10:56 AM Madison Kelly [EMAIL PROTECTED] writes: Here is my full query: tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; This is my index (which I guess is wrong): tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 The index is fine, but you need to phrase the query as ... ORDER BY file_type, file_parent_dir, file_name; (Whether you use ASC or not doesn't matter.) Otherwise the planner won't make the connection to the sort ordering of the index. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index ot being used
Kevin Grittner wrote: It sure would be nice if the optimizer would consider that it had the leeway to add any column which was restricted to a single value to any point in the ORDER BY clause. Without that, the application programmer has to know what indexes are on the table, rather than being able to just worry about the set of data they want. Obviously, if a column can have only one value in the result set, adding to any point in the ORDER BY can't change anything but performance. That sure sounds like something which should fall within the scope of an optimizer. It really should be a DBA function to add or drop indexes to tune the performance of queries, without requiring application programmers to modify the queries for every DBA adjustment. (When you have a database with over 350 tables and thousands of queries, you really begin to appreciate the importance of this.) I agree that having a smarter optimizer, which can recognize when an index can be used for ORDER BY would be useful. I don't know if there are specific reasons why not, other than just not being implemented yet. It might be tricky to get it correct (for instance, how do you know which columns can be added, which ones will be constant) Perhaps you could just potentially add the WHERE items if they have an equality constraint with a constant. But I'm guessing there are more cases than that where the optimization could be performed. Also, the more options you give the planner, the longer it takes on average to plan any single query. Yes, it is beneficial for this use case, but does that balance out slowing down all the other queries by a tiny bit. I'm guessing the optimization wasn't as important as some of the others that have been done, so it hasn't been implemented yet. John =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Index ot being used
On Mon, Jun 13, 2005 at 09:51:57 -0500, John A Meinel [EMAIL PROTECTED] wrote: I don't know if there are specific reasons why not, other than just not being implemented yet. It might be tricky to get it correct (for instance, how do you know which columns can be added, which ones will be constant) Perhaps you could just potentially add the WHERE items if they have an equality constraint with a constant. But I'm guessing there are more cases than that where the optimization could be performed. I think there is already some intelligence about which expressions are constant in particular parts of a plan. I think you need to be able to do two things. One is to drop constant expressions from order by lists. The other is when looking for an index to produce a specific ordering, to ingore leading constant expressions when comparing to the order by expressions. Also, the more options you give the planner, the longer it takes on average to plan any single query. Yes, it is beneficial for this use case, but does that balance out slowing down all the other queries by a tiny bit. But there aren't that many possible indexes, so I don't expect this will slow things down much more than the current check for potentially useful indexes. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Index ot being used
John A Meinel [EMAIL PROTECTED] wrote: I don't know if there are specific reasons why not, other than just not being implemented yet. It might be tricky to get it correct Not so much tricky to get correct, as potentially expensive to test for; it'd be quite easy to waste a lot of cycles trying to match ORDER BY keys in multiple ways to completely-irrelevant indexes. Since this will only be helpful for a minority of queries but the costs would be paid on almost everything with an ORDER BY, that consideration has been looming large in my mind. Bruno Wolff III [EMAIL PROTECTED] writes: I think you need to be able to do two things. One is to drop constant expressions from order by lists. The other is when looking for an index to produce a specific ordering, to ingore leading constant expressions when comparing to the order by expressions. I've been thinking about this some more this morning, and I think I see how it could be relatively inexpensive to recognize x=constant restrictions that allow ordering columns of an index to be ignored. We are already doing 90% of the work for that just as a byproduct of trying to match the x=constant clause to the index in the first place, so it's mostly a matter of refactoring the code to allow that work to be reused. I don't, however, see an equally inexpensive way to ignore ORDER BY columns. That would imply associating the '=' operator of the restriction clause with the '' or '' operator of the ORDER BY clause, which means searching for a btree opclass that has them in common, which is not cheap since there's no indexing on pg_amop that would allow us to find it easily. (There are various places where we do in fact do that sort of thing, but they aren't so performance-critical.) This doesn't come up in the other case because we already know the relevant opclass from the index. I don't think the use-case has been shown that justifies doing this much work to ignore useless ORDER BY clauses. The examples that have come up in the past all suggest ignoring index columns not the other way 'round. Can you make a case that we need to do that part of it? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Index ot being used
On Mon, Jun 13, 2005 at 12:22:14 -0400, Tom Lane [EMAIL PROTECTED] wrote: I don't think the use-case has been shown that justifies doing this much work to ignore useless ORDER BY clauses. The examples that have come up in the past all suggest ignoring index columns not the other way 'round. Can you make a case that we need to do that part of it? I don't think so. I don't think people are likely to order by constant expressions except by adding them to the front to help optimization. When I was thinking about this I was looking at what equivalences could be used and didn't look back to see which ones would be useful in the normal case. And I think it is a lot more likely people will leave out columns they know not to be relevant than to include them. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index ot being used
I agree that ignoring useless columns in an ORDER BY clause is less important than ignoring index columns where the value is fixed. There is one use case for ignoring useless ORDER BY columns that leaps to mind, however -- a column is added to the ORDER BY clause of a query to help out the optimizer, then the indexes are modified such that that column is no longer useful. Whether this merits the programming effort and performance hit you describe seems highly questionable, though. -Kevin Tom Lane [EMAIL PROTECTED] 06/13/05 11:22 AM I don't think the use-case has been shown that justifies doing this much work to ignore useless ORDER BY clauses. The examples that have come up in the past all suggest ignoring index columns not the other way 'round. Can you make a case that we need to do that part of it? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Index ot being used
On Mon, Jun 13, 2005 at 11:46:46 -0500, Kevin Grittner [EMAIL PROTECTED] wrote: I agree that ignoring useless columns in an ORDER BY clause is less important than ignoring index columns where the value is fixed. There is one use case for ignoring useless ORDER BY columns that leaps to mind, however -- a column is added to the ORDER BY clause of a query to help out the optimizer, then the indexes are modified such that that column is no longer useful. Whether this merits the programming effort and performance hit you describe seems highly questionable, though. I suspect that this isn't a big deal. There was a question like that that has been going back and forth over the last couple of days. If you remove the constant expression from the index, you aren't likely going to use the index anyway, but will instead sort the output rows from either a sequential scan or an index scan based on an index that does use the constant expression. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Index ot being used
Bruno Wolff III wrote: I am guessing you mean 'file_type' instead of 'file_info'. To do this efficiently you want an index on (file_type, file_parent_dir, file_name). Currently you only have an index on (file_parent_dir, file_name) which won't help for this query. You also need to order by file_type even though it will be constant for all of the returned rows in order to help out the planner. This will allow an index scan over the desired rows that returns them in the desired order. Please actually try this before changing anything else. If I follow then I tried it but still got the sequential scan. Here's the index and query (copied from the 'psql' shell): tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual time=15523.767..15731.136 rows=25795 loops=1) Sort Key: file_type, file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490 width=119) (actual time=0.132..2164.757 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 15884.188 ms (5 rows) If I follow all three 'ORDER BY...' items match the three columns in the index. Again, thanks! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index ot being used
Kevin Grittner wrote: tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 The index is fine, but you need to phrase the query as ... ORDER BY file_type, file_parent_dir, file_name; (Whether you use ASC or not doesn't matter.) Otherwise the planner won't make the connection to the sort ordering of the index. regards, tom lane With Bruno's help I've gone back and tried just this with no luck. I've re-written the query to include all three items in the 'ORDER BY...' column in the same order but the sort still takes a long time and a sequential scan is being done instead of using the index. For what it's worth, and being somewhat of a n00b, I agree with the idea of a smarter, more flexible planner. I guess the trade off is the added overhead neaded versus the size of the average query. Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Index ot being used
Madison Kelly [EMAIL PROTECTED] writes: Bruno Wolff III wrote: Please actually try this before changing anything else. If I follow then I tried it but still got the sequential scan. Given the fairly large number of rows being selected, it seems likely that the planner thinks this is faster than an indexscan. It could be right, too. Have you tried set enable_seqscan = off to see if the index is used then? If so, is it faster or slower? Comparing EXPLAIN ANALYZE results with enable_seqscan on and off would be useful. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index ot being used
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: Bruno Wolff III wrote: Please actually try this before changing anything else. If I follow then I tried it but still got the sequential scan. Given the fairly large number of rows being selected, it seems likely that the planner thinks this is faster than an indexscan. It could be right, too. Have you tried set enable_seqscan = off to see if the index is used then? If so, is it faster or slower? Comparing EXPLAIN ANALYZE results with enable_seqscan on and off would be useful. Wow! With the sequence scan off my query took less than 2sec. When I turned it back on the time jumped back up to just under 14sec. tle-bu= set enable_seqscan = off; SET tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Index Scan using file_info_7_display_idx on file_info_7 (cost=0.00..83171.78 rows=25490 width=119) (actual time=141.405..1700.459 rows=25795 loops=1) Index Cond: ((file_type)::text = 'd'::text) Total runtime: 1851.366 ms (3 rows) tle-bu= set enable_seqscan = on; SET tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual time=13605.185..13728.436 rows=25795 loops=1) Sort Key: file_type, file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490 width=119) (actual time=0.048..2018.996 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 13865.830 ms (5 rows) So the index obiously provides a major performance boost! I just need to figure out how to tell the planner how to use it... Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index ot being used
On Mon, Jun 13, 2005 at 15:05:00 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Wow! With the sequence scan off my query took less than 2sec. When I turned it back on the time jumped back up to just under 14sec. tle-bu= set enable_seqscan = off; SET tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Index Scan using file_info_7_display_idx on file_info_7 (cost=0.00..83171.78 rows=25490 width=119) (actual time=141.405..1700.459 rows=25795 loops=1) Index Cond: ((file_type)::text = 'd'::text) Total runtime: 1851.366 ms (3 rows) tle-bu= set enable_seqscan = on; SET tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual time=13605.185..13728.436 rows=25795 loops=1) Sort Key: file_type, file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490 width=119) (actual time=0.048..2018.996 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 13865.830 ms (5 rows) So the index obiously provides a major performance boost! I just need to figure out how to tell the planner how to use it... The two things you probably want to look at are (in postgresql.conf): effective_cache_size = 1# typically 8KB each random_page_cost = 2# units are one sequential page fetch cost Increasing effective cache size and decreasing the penalty for random disk fetches will favor using index scans. People have reported that dropping random_page_cost from the default of 4 to 2 works well. Effective cache size should be set to some reasonable estimate of the memory available on your system to postgres, not counting that set aside for shared buffers. However, since the planner thought the index scan plan was going to be 6 times slower than the sequential scan plan, I don't know if tweaking these values enough to switch the plan choice won't cause problems for other queries. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index ot being used
Madison Kelly [EMAIL PROTECTED] writes: So the index obiously provides a major performance boost! I just need to figure out how to tell the planner how to use it... Be careful extrapolating too much from a single query in a single context. Notably you might want to test the same query after not touching this table for a little while. The index is probably benefiting disproportionately from having you repeatedly running this one query and having the entire table in cache. That said, you should look at lowering random_page_cost. The default is 4 but if this query is representative of your system's performance then much of your database is in cache and the effective value will be closer to 1. Try 2 or even 1.5 or 1.2. But like I said, test other queries and test under more representative conditions other than repeating a single query over and over. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index ot being used
Madison Kelly [EMAIL PROTECTED] writes: So the index obiously provides a major performance boost! I just need to figure out how to tell the planner how to use it... Simple division shows that the planner's cost estimate ratio between the seqscan and the indexscan (11956.84 vs 83171.78) is off by a factor of more than 8 compared to reality (2018.996 vs 1700.459). Also the cost of the sort seems to be drastically underestimated. I suspect this may be a combination of random_page_cost being too high (since your test case, at least, is no doubt fully cached in RAM) and cpu_operator_cost being too low. I'm wondering if text comparisons are really slow on your machine --- possibly due to strcoll being inefficient in the locale you are using, which you didn't say. That would account for both the seqscan being slower than expected and the sort taking a long time. It'd be interesting to look at the actual runtimes of this seqscan vs one that is doing a simple integer comparison over the same number of rows (and, preferably, returning about the same number of rows as this). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Pseudo-Solved was: (Re: [PERFORM] Index ot being used)
Bruno Wolff III wrote: On Mon, Jun 13, 2005 at 15:05:00 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Wow! With the sequence scan off my query took less than 2sec. When I turned it back on the time jumped back up to just under 14sec. tle-bu= set enable_seqscan = off; SET tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Index Scan using file_info_7_display_idx on file_info_7 (cost=0.00..83171.78 rows=25490 width=119) (actual time=141.405..1700.459 rows=25795 loops=1) Index Cond: ((file_type)::text = 'd'::text) Total runtime: 1851.366 ms (3 rows) tle-bu= set enable_seqscan = on; SET tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual time=13605.185..13728.436 rows=25795 loops=1) Sort Key: file_type, file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490 width=119) (actual time=0.048..2018.996 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 13865.830 ms (5 rows) So the index obiously provides a major performance boost! I just need to figure out how to tell the planner how to use it... The two things you probably want to look at are (in postgresql.conf): effective_cache_size = 1# typically 8KB each random_page_cost = 2# units are one sequential page fetch cost Increasing effective cache size and decreasing the penalty for random disk fetches will favor using index scans. People have reported that dropping random_page_cost from the default of 4 to 2 works well. Effective cache size should be set to some reasonable estimate of the memory available on your system to postgres, not counting that set aside for shared buffers. However, since the planner thought the index scan plan was going to be 6 times slower than the sequential scan plan, I don't know if tweaking these values enough to switch the plan choice won't cause problems for other queries. Hmm, In this case I am trying to avoid modifying 'postgres.conf' and am trying to handle any performance tweaks within my program through SQL calls. This is because (I hope) my program will be installed by many users and I don't want to expect them to be able/comfortable playing with 'postgres.conf'. I do plan later though to create a section in the docs with extra tweaks for more advanced users and in that case I will come back to this and try/record just that. In the mean time Tom's recommendation works from perl by calling: $DB-do(SET ENABLE_SEQSCAN TO OFF) || die... query... $DB-do(SET ENABLE_SEQSCAN TO ON) || die... Forces the index to be used. It isn't clean but it works for now and I don't need to do anything outside my program. Lacking any other ideas, thank you very, very much for sticking with this and helping me out! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Index ot being used
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: So the index obiously provides a major performance boost! I just need to figure out how to tell the planner how to use it... Simple division shows that the planner's cost estimate ratio between the seqscan and the indexscan (11956.84 vs 83171.78) is off by a factor of more than 8 compared to reality (2018.996 vs 1700.459). Also the cost of the sort seems to be drastically underestimated. I suspect this may be a combination of random_page_cost being too high (since your test case, at least, is no doubt fully cached in RAM) and cpu_operator_cost being too low. I'm wondering if text comparisons are really slow on your machine --- possibly due to strcoll being inefficient in the locale you are using, which you didn't say. That would account for both the seqscan being slower than expected and the sort taking a long time. It'd be interesting to look at the actual runtimes of this seqscan vs one that is doing a simple integer comparison over the same number of rows (and, preferably, returning about the same number of rows as this). regards, tom lane This is where I should mention that though 'n00b' might be a little harsh, I am still somewhat of a beginner (only been using postgres or programming at all for a little over a year). What is, and how do I check, 'strcoll'? Is there a way that I can clear the psql cache to make the tests more accurate to real-world situations? For what it's worth, the program is working (I am doing stress-testing and optimizing now) and the data in this table is actual data, not a construct. As I mentioned to Bruno in my reply to him, I am trying to keep as many tweaks as I can inside my program. The reason for this is that this is a backup program that I am trying to aim to more mainstream users or where a techy would set it up and then it would be used by mainstream users. At this point I want to avoid, as best I can, any changes from default to the 'postgres.conf' file or other external files. Later though, once I finish this testing phase, I plan to write a section of external tweaking where I will test these changes out and note my success for mre advanced users who feel more comfortable playing with postgres (and web server, rsync, etc) configs. If there is any way that I can make changes like this similar from inside my (perl) program I would prefer that. For example, I implemented the 'enable_seqscan' via: $DB-do(SET ENABLE_SEQSCAN TO OFF) || die... ... $DB-do(SET ENABLE_SEQSCAN TO ON) || die... Thank you very kindly! You and Bruno are wonderfully helpful! (as are the other's who have replied ^_^;) Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index ot being used
Tobias Brox wrote: [EMAIL PROTECTED] - Fri at 12:10:19PM -0400] tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM file_info_7; QUERY PLAN -- Seq Scan on file_info_7 (cost=0.00..11028.35 rows=294035 width=118) (actual time=0.122..2707.764 rows=294035 loops=1) Total runtime: 3717.862 ms (2 rows) As far as I can see, you are selecting everything from the table without any sort order. The only rational thing to do then is a sequential scan, it's no point in an index scan. Thanks for replying, Tobias and Jacques! Doh! This is a case of over simplification, I think. I was trying to simplify my query as much as I could and then work it out to the actual query I want. It would seem I don't understand how to use indexes quite right. Do you think you might be able to help me with a useful index? Here is the 'file_info_7' schema, my query and the 'explain analyze' results: tle-bu= \d file_info_7 Table public.file_info_7 Column| Type |Modifiers --+--+- file_group_name | text | file_group_uid | bigint | not null file_mod_time| bigint | not null file_name| text | not null file_parent_dir | text | not null file_perm| text | not null file_size| bigint | not null file_type| character varying(2) | not null default 'f'::character varying file_user_name | text | file_user_uid| bigint | not null file_backup | boolean | not null default true file_display | boolean | not null default false file_restore_display | boolean | not null default false file_restore | boolean | not null default false Indexes: file_info_7_display_idx btree (file_type, file_parent_dir, file_name) Here is my full query: tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; QUERY PLAN - Sort (cost=14541.24..14603.48 rows=24895 width=118) (actual time=15751.804..15967.591 rows=25795 loops=1) Sort Key: file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11763.44 rows=24895 width=118) (actual time=19.289..3840.845 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 16043.075 ms (5 rows) This is my index (which I guess is wrong): tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 Those are the three columns I am using in my restrictions so I thought that would create an index this query would use. Do I need to do something different because of the 'ORDER BY...'? Thanks again for the replies! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Index ot being used
Madison Kelly [EMAIL PROTECTED] writes: Here is my full query: tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; This is my index (which I guess is wrong): tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 The index is fine, but you need to phrase the query as ... ORDER BY file_type, file_parent_dir, file_name; (Whether you use ASC or not doesn't matter.) Otherwise the planner won't make the connection to the sort ordering of the index. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index ot being used
On Sun, Jun 12, 2005 at 10:12:27 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Indexes: file_info_7_display_idx btree (file_type, file_parent_dir, file_name) Here is my full query: tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; QUERY PLAN - This is a case where postgres's planner can't make a deduction needed for it to realize that the index can be used. Try rewriting the query as: SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; ---(end of broadcast)--- TIP 3: 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: [PERFORM] Index ot being used
Tom Lane wrote: Madison Kelly [EMAIL PROTECTED] writes: Here is my full query: tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; This is my index (which I guess is wrong): tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 The index is fine, but you need to phrase the query as ... ORDER BY file_type, file_parent_dir, file_name; (Whether you use ASC or not doesn't matter.) Otherwise the planner won't make the connection to the sort ordering of the index. regards, tom lane Hi Tom and Bruno, After sending that email I kept plucking away and in the course of doing so decided that I didn't need to return the 'file_type' column. Other than that, it would see my query now matches what you two have recommended in the 'ORDER BY...' front but I still can't get an index search. Here is the latest query and the new index: tle-bu= \d file_info_7_display_idx; Index public.file_info_7_display_idx Column | Type -+-- file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_parent_dir, file_name, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=14509.53..14571.76 rows=24895 width=114) (actual time=19995.250..20123.874 rows=25795 loops=1) Sort Key: file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11762.44 rows=24895 width=114) (actual time=0.123..3228.446 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 20213.443 ms The 'Sort' is taking 20 seconds on my pentium III 1GHz (not great, but...). If I follow you right, my index is 'file_parent_dir' first and 'file_name' second (does order matter?). So I figured the query: SELECT file_parent_dir, file_name, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; Would hit the index for the sort. Is there any other way other than 'EXPLAIN ANALYZE...' to get a better understanding of what is happening in there? For what it's worth, there is a little under 300,000 entries in this table of which, as you can see above, 25,795 are being returned. Yet again, thank you both!! I'm off to keep trying to figure this out... Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Index ot being used
On Sun, Jun 12, 2005 at 18:52:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: After sending that email I kept plucking away and in the course of doing so decided that I didn't need to return the 'file_type' column. Other than that, it would see my query now matches what you two have recommended in the 'ORDER BY...' front but I still can't get an index search. No it doesn't. Even if you don't return file_type you still need it in the order by clause if you want postgres to consider using your index. Is there some reason you didn't actually try out our suggestion, but are now asking for more advice? Here is the latest query and the new index: tle-bu= \d file_info_7_display_idx; Index public.file_info_7_display_idx Column | Type -+-- file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_parent_dir, file_name, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Index ot being used
On Sun, Jun 12, 2005 at 22:00:01 -0500, Bruno Wolff III [EMAIL PROTECTED] wrote: On Sun, Jun 12, 2005 at 18:52:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: After sending that email I kept plucking away and in the course of doing so decided that I didn't need to return the 'file_type' column. Other than that, it would see my query now matches what you two have recommended in the 'ORDER BY...' front but I still can't get an index search. No it doesn't. Even if you don't return file_type you still need it in the order by clause if you want postgres to consider using your index. I didn't notice that you had changed the index. The reason this index doesn't help is that you can't use it to select on records with the desired file_type. Is there some reason you didn't actually try out our suggestion, but are now asking for more advice? Here is the latest query and the new index: tle-bu= \d file_info_7_display_idx; Index public.file_info_7_display_idx Column | Type -+-- file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_parent_dir, file_name, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index ot being used
Bruno Wolff III wrote: On Sun, Jun 12, 2005 at 18:52:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: After sending that email I kept plucking away and in the course of doing so decided that I didn't need to return the 'file_type' column. Other than that, it would see my query now matches what you two have recommended in the 'ORDER BY...' front but I still can't get an index search. No it doesn't. Even if you don't return file_type you still need it in the order by clause if you want postgres to consider using your index. Is there some reason you didn't actually try out our suggestion, but are now asking for more advice? No good excuse. I'll recreate the index and test out your suggestion... tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=14789.92..14857.06 rows=26856 width=117) (actual time=16865.473..16989.104 rows=25795 loops=1) Sort Key: file_type, file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11762.44 rows=26856 width=117) (actual time=0.178..1920.413 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 17102.925 ms (5 rows) tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 I'm still getting the sequential scan. Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Index ot being used
On Sun, Jun 12, 2005 at 23:42:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: As you probably saw in my last reply, I went back to the old index and tried the query you and Tom Lane recommended. Should this not have caught the index? Probably, but there might be some other reason the planner thought it was better to not use it. Using indexes is not always faster. It would help to see your latest definition of the table and indexes, the exact query you used and explain analyze output. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index ot being used
Bruno Wolff III wrote: On Sun, Jun 12, 2005 at 23:42:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: As you probably saw in my last reply, I went back to the old index and tried the query you and Tom Lane recommended. Should this not have caught the index? Probably, but there might be some other reason the planner thought it was better to not use it. Using indexes is not always faster. It would help to see your latest definition of the table and indexes, the exact query you used and explain analyze output. Okay, here's what I have at the moment: tle-bu= \d file_info_7 Table public.file_info_7 Column| Type |Modifiers --+--+- file_group_name | text | file_group_uid | bigint | not null file_mod_time| bigint | not null file_name| text | not null file_parent_dir | text | not null file_perm| text | not null file_size| bigint | not null file_type| character varying(2) | not null default 'f'::character varying file_user_name | text | file_user_uid| bigint | not null file_backup | boolean | not null default true file_display | boolean | not null default false file_restore_display | boolean | not null default false file_restore | boolean | not null default false Indexes: file_info_7_display_idx btree (file_parent_dir, file_name) tle-bu= \d file_info_7_display_idx Index public.file_info_7_display_idx Column | Type -+-- file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC; QUERY PLAN Sort (cost=15091.53..15165.29 rows=29502 width=114) (actual time=12834.933..12955.136 rows=25795 loops=1) Sort Key: file_parent_dir, file_name - Seq Scan on file_info_7 (cost=0.00..11762.44 rows=29502 width=114) (actual time=0.244..2533.388 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 13042.421 ms (5 rows) Since my last post I went back to a query closer to what I actually want. What is most important to me is that 'file_parent_dir, file_name, file_display' are returned and that the results are sorted by 'file_parent_dir, file_name' and the results are restricted to where 'file_info='d''. Basically what I am trying to do is display a directory tree in a file browser. I had this working before but it was far, far too slow once the number of directories to display got much higher than 1,000. That is what 'file_display' is, by the way. Again, thank you! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Index ot being used
Hi all, I have an index on a table that doesn't seem to want to be used. I'm hopig someone might be able to help point me in the right direction. My index is (typed, not copied): tle-bu= \d file_info_7_display_idx; Index public.file_info_7_display_idx Column | Type -+-- file_type | character varying(2) file_parent_dir | text file_name | text btree, for table public.file_info_7 tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM file_info_7; QUERY PLAN -- Seq Scan on file_info_7 (cost=0.00..11028.35 rows=294035 width=118) (actual time=0.122..2707.764 rows=294035 loops=1) Total runtime: 3717.862 ms (2 rows) Can anyone see what's wrong? Should I post the table schema? Thanks all! Madison ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index ot being used
[EMAIL PROTECTED] - Fri at 12:10:19PM -0400] tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM file_info_7; QUERY PLAN -- Seq Scan on file_info_7 (cost=0.00..11028.35 rows=294035 width=118) (actual time=0.122..2707.764 rows=294035 loops=1) Total runtime: 3717.862 ms (2 rows) As far as I can see, you are selecting everything from the table without any sort order. The only rational thing to do then is a sequential scan, it's no point in an index scan. -- Tobias Brox, +47-91700050 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index ot being used
Hi, At 18:10 10/06/2005, [EMAIL PROTECTED] wrote: tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM file_info_7; What could the index be used for? Unless you have some WHERE or (in some cases) ORDER BY clause, there's absolutely no need for an index, since you are just asking for all rows from the table... Jacques. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
