Re: [PERFORM] Slow query with planner row strange estimation
phb07 a écrit : Dimitri a écrit : It's probably one of the cases when having HINTS in PostgreSQL may be very helpful.. SELECT /*+ enable_nestloop=off */ ... FROM ... will just fix this query without impacting other queries and without adding any additional instructions into the application code.. So, why there is a such resistance to implement hints withing SQL queries in PG?.. Rgds, -Dimitri +1. Another typical case when it would be helpful is with setting the cursor_tuple_fraction GUC variable for a specific statement, without being obliged to issue 2 SET statements, one before the SELECT and the other after. I remember that the dimension columns of the fact table have indexes like with WHERE IS NOT NULL on the column indexed. Example: CREATE INDEX dwhinv_pd2_idx ON dwhinv USING btree (dwhinv_p2rfodstide) TABLESPACE tb_index WHERE dwhinv_p2rfodstide IS NOT NULL; Is the where clause being used to select the sample rows on which the stats will be calculated or just used to exclude values after collecting stat ? As I am writing I realize there's must be no link between a table column stats and an index a the same column. (By the way, If I used is not null on each column with such an index, it changes nothing) About the oracle-like hints, it does not really help, because the query is generated in an external jar that I should fork to include the modification. I would prefer forcing a plan based on the query hashcode, but this does not fix what make the planner goes wrong. -- HOSTIN Damien - Equipe RD Tel:+33(0)4 63 05 95 40 Société Axège 23 rue Saint Simon 63000 Clermont Ferrand www.axege.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Queries with conditions using bitand operator
Hi, I have table ARTICLE containing a String a field STATUS that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article where status 4 = 4). Thus i'm facing performance problemes with these select queries: the queries are too slow. Since i'm using the BITAND operator in my conditions, creating an index on the status filed is useless and since the second operator variable (status 4 = 4; status 8 = 8; status 16 = 16...) a functional index is also usless (because a functional index require the use of a function that accept only table column as input parameter: constants are not accepted). So is there a way to enhance the performance of these queries? Thanks, Elias -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Tom Lane t...@sss.pgh.pa.us writes: I agree with the comments to the effect that this is really a packaging and documentation problem. There is no need for us to re-invent the existing solutions, but there is a need for making sure that they are readily available and people know when to use them. On this topic, I think we're getting back to the idea of having non-core daemon helpers that should get supervised the way postmaster already does with backends wrt starting and stoping them at the right time. So a supervisor daemon with a supervisor API that would have to support autovacuum as a use case, then things like pgagent, PGQ and pgbouncer, would be very welcome. What about starting a new thread about that? Or you already know you won't want to push the extensibility of PostgreSQL there? Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
On Tue, Jul 13, 2010 at 16:42, Dimitri Fontaine dfonta...@hi-media.com wrote: Tom Lane t...@sss.pgh.pa.us writes: I agree with the comments to the effect that this is really a packaging and documentation problem. There is no need for us to re-invent the existing solutions, but there is a need for making sure that they are readily available and people know when to use them. On this topic, I think we're getting back to the idea of having non-core daemon helpers that should get supervised the way postmaster already does with backends wrt starting and stoping them at the right time. So a supervisor daemon with a supervisor API that would have to support autovacuum as a use case, then things like pgagent, PGQ and pgbouncer, would be very welcome. What about starting a new thread about that? Or you already know you won't want to push the extensibility of PostgreSQL there? +1 on this idea in general, if we can think up a good API - this seems very useful to me, and you have some good examples there of cases where it'd definitely be a help. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Queries with conditions using bitand operator
On 07/13/2010 06:48 AM, Elias Ghanem wrote: Hi, I have table ARTICLE containing a String a field STATUS that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article where status 4 = 4). Thus i'm facing performance problemes with these select queries: the queries are too slow. Since i'm using the BITAND operator in my conditions, creating an index on the status filed is useless and since the second operator variable (status 4 = 4; status 8 = 8; status 16 = 16...) a functional index is also usless (because a functional index require the use of a function that accept only table column as input parameter: constants are not accepted). So is there a way to enhance the performance of these queries? Thanks, Elias How many flags are there? If its not too many you could make a separate column for each... but then that would be lots of indexes too... One other thought I had was to make it a text column, turn the flags into words (space separated) and use full text indexes. I played around with int's and string's but I couldnt find a way using the operator. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] performance on new linux box
On 07/11/2010 03:02 PM, Ryan Wexler wrote: Well I got me a new raid card, MegaRAID 8708EM2, fully equipped with BBU and read and write caching are enabled. It completely solved my performance problems. Now everything is way faster than the previous server. Thanks for all the help everyone. One question I do have is this card has a setting called Read Policy which apparently helps with sequentially reads. Do you think that is something I should enable? I would think it depends on your usage. If you use clustered indexes (and understand how/when they help) then enabling it would help (cuz clustered is assuming sequential reads). or if you seq scan a table, it might help (as long as the table is stored relatively close together). But if you have a big db, that doesnt fit into cache, and you bounce all over the place doing seeks, I doubt it'll help. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Queries with conditions using bitand operator
On 07/13/2010 04:48 AM, Elias Ghanem wrote: Hi, I have table ARTICLE containing a String a field STATUS that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article where status 4 = 4). Thus i'm facing performance problemes with these select queries: the queries are too slow. Since i'm using the BITAND operator in my conditions, creating an index on the status filed is useless and since the second operator variable (status 4 = 4; status 8 = 8; status 16 = 16...) a functional index is also usless (because a functional index require the use of a function that accept only table column as input parameter: constants are not accepted). So is there a way to enhance the performance of these queries? You haven't given a lot of info to help us help you, but would something along these lines be useful to you? drop table if exists testbit; create table testbit( id serial primary key, article text, status int ); insert into testbit (article, status) select 'article ' || generate_series::text, generate_series % 256 from generate_series(1,100); create index idx1 on testbit(article) where status 1 = 1; create index idx2 on testbit(article) where status 2 = 2; create index idx4 on testbit(article) where status 4 = 4; create index idx8 on testbit(article) where status 8 = 8; create index idx16 on testbit(article) where status 16 = 16; create index idx32 on testbit(article) where status 512 = 512; update testbit set status = status + 512 where id in (42, 4242, 424242); explain analyze select * from testbit where status 512 = 512; QUERY PLAN -- Index Scan using idx32 on testbit (cost=0.00..4712.62 rows=5000 width=22) (actual time=0.080..0.085 rows=3 loops=1) Total runtime: 0.170 ms HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, Support signature.asc Description: OpenPGP digital signature
