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
[PERFORM] Updates on large tables are extremely slow
Hi, I'm trying to update a table that has about 600.000 records. The update query is very simple :update mytable set pagesdesc = - pages ; (I use pagesdesc to avoid problems with sort that have one field in ascending order and one in descending order. That was a problem I had a week ago) The query takes about half an hour to an hour to execute. I have tried a lot of things. This is my setup Linux Slackware 10.1 Postgres 8.0.1 My filesystem has EXT2 filesystem so I don't have journaling. My partition is mounted in fstab with the noatime option. I have tried to change some settings in $PGDATA/postgresql.conf. But that does not seem to matter a lot. I'm not even sure that file is being used. I ran KSysGuard when executing my query and I don't see my processor being used more than 20% The memory increases for the cache, but not for the app itself. My testsystem is an Asus portable, P4 with 1 Gig of RAM. Disk is speedy. All runs fine except for the update queries. I would appreciate some help or a document to point me to the settings I must change. Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <> Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. ---(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] Updates on large tables are extremely slow
Hi, At 19:40 12/06/2005, Yves Vindevogel wrote: Hi, I'm trying to update a table that has about 600.000 records. The update query is very simple :update mytable set pagesdesc = - pages ; (I use pagesdesc to avoid problems with sort that have one field in ascending order and one in descending order. That was a problem I had a week ago) An index on (-pages) would probably do exactly what you want without having to add another column. The query takes about half an hour to an hour to execute. Depending on the total size of the table and associated indexes and on your exact setup (especially your hardare), this could be quite normal: the exuctor goes through all rows in the table, and for each, creates a copy with the additional column, updates indexes, and logs to WAL. You might want to look into moving your WAL files (pg_xlog) to a separate disk, increase WAL and checkpoint buffers, add more RAM, add more disks... But as I said, you might not even need to do that, just use an index on an expression... Jacques. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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
[PERFORM] Resource Requirements
I've got a list of old resource requirements. I want to know how far off they are and if anything crucial is missing. My usual recommendation is "as much as you can afford" so I don't usually deal with real numbers :) RAM: Number of connections * 2MB Disk: Program and Manual 8-15MB Regression Tests 30MB Compiled Source 60-160MB Storage for user data ( as much as you can afford :) Please copy me since I'm not officially on this list. Thanks, Elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = I have always depended on the [QA] of strangers. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Updates on large tables are extremely slow
Yves Vindevogel wrote: I'm trying to update a table that has about 600.000 records. The update query is very simple : update mytable set pagesdesc = - pages ; The query takes about half an hour to an hour to execute. I have tried a lot of things. Half an hour seem a bit long - I would expect less than 5 minutes on reasonable hardware. You may have dead tuple bloat - can you post the output of 'ANALYZE VERBOSE mytable' ? Cheers Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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
Bruno Wolff III wrote: 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. 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? At any rate, I am re-reading the documents on indexing for 7.4.x on postgresql.org... This is kind of flustering. Thanks again though for som much help! 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
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
