Re: [PERFORM] how to change the index chosen in plan?

于 2012/6/9 0:39, Kevin Grittner 写道: Rural Hunter wrote: How can adjust the statistics target? default_statistics_target http://www.postgresql.org/docs/current/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER or ALTER TABLE x ALTER COLUMN y SET STATISTICS n http

Re: [PERFORM] pg 9.1 brings host machine down

Thanks alot. I've tried to play with work_mem and after few days of the production testing pg behaves much better. See no more files in the pgsql_tmp folder. pg processes consumes reasonable memory, no swap operation any more. I've studied official pg docs about work_mem an still have no idea which

Re: [PERFORM] how to change the index chosen in plan?

Rural Hunter wrote: > How can adjust the statistics target? default_statistics_target http://www.postgresql.org/docs/current/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER or ALTER TABLE x ALTER COLUMN y SET STATISTICS n http://www.postgresql.org/docs/current/interact

Re: [PERFORM] how to change the index chosen in plan?

Hi Kevin, Thanks for your detailed explanation. 于 2012/6/8 22:37, Kevin Grittner 写道: Rural Hunter wrote: 于2012年6月8日 22:10:58,Tom Lane写到: Rural Hunter writes: I have a query like this: select a.* from a inner join b on a.aid=b.aid where a.col1=33 a.col2=44 and b.bid=8 postgresql selected the

Re: [PERFORM] how to change the index chosen in plan?

Rural Hunter wrote: > 于2012年6月8日 22:10:58,Tom Lane写到: >> Rural Hunter writes: >>> I have a query like this: >>> select a.* from a inner join b on a.aid=b.aid where a.col1=33 >>> a.col2=44 and b.bid=8 >>> postgresql selected the index on a.col1 then selected the index >>> on b.bid. But in my situa

Re: [PERFORM] non index use on LIKE on a non pattern string

Le vendredi 8 juin 2012 15:57:07, Tom Lane a écrit : > =?iso-8859-1?q?C=E9dric_Villemain?= writes: > >> I have noticed that with a SELECT query containing the following > >> constraint: > >> > >> column LIKE ? > >> > >> and an index on that column, PostgreSQL will not use the index > >> even if

Re: [PERFORM] how to change the index chosen in plan?

No, it's not the analyze problem. For some other values on b.bid such as 9, 10, the plan is fine since there a a lot of rows in table b for them. But for some specific values such as 8 I want the plan changed. 于2012年6月8日 22:10:58,Tom Lane写到: Rural Hunter writes: I have a query like this: sel

Re: [PERFORM] how to change the index chosen in plan?

Rural Hunter writes: > I have a query like this: > select a.* from a inner join b on a.aid=b.aid where a.col1=33 a.col2=44 > and b.bid=8 > postgresql selected the index on a.col1 then selected the index on > b.bid. But in my situation, I know that the query will be faster if it > chose the inde

Re: [PERFORM] non index use on LIKE on a non pattern string

=?iso-8859-1?q?C=E9dric_Villemain?= writes: >> I have noticed that with a SELECT query containing the following >> constraint: >> >> column LIKE ? >> >> and an index on that column, PostgreSQL will not use the index >> even if the parameter doesn't contain special pattern characters >> such as %

[PERFORM] how to change the index chosen in plan?

I have a query like this: select a.* from a inner join b on a.aid=b.aid where a.col1=33 a.col2=44 and b.bid=8 postgresql selected the index on a.col1 then selected the index on b.bid. But in my situation, I know that the query will be faster if it chose the index on b.bid first since there are

Re: [PERFORM] non index use on LIKE on a non pattern string

> I have noticed that with a SELECT query containing the following > constraint: > > column LIKE ? > > and an index on that column, PostgreSQL will not use the index > even if the parameter doesn't contain special pattern characters > such as %. you should have a postgresql 8.3,isn't it ? l

[PERFORM] non index use on LIKE on a non pattern string

Hello, I have noticed that with a SELECT query containing the following constraint: column LIKE ? and an index on that column, PostgreSQL will not use the index even if the parameter doesn't contain special pattern characters such as %. >From PG POV it might be logical, because, who is stup

Re: [PERFORM] Seqscan slowness and stored procedures

On 8 June 2012 11:58, Albe Laurenz wrote: > Did you take caching of table data in the buffer cache or the filesystem > cache into account?  Did you run your tests several times in a row and > were the actual execution times consistent? Yes, and yes. >> Would tweaking enable_seqscan and other pl

Re: [PERFORM] Multiple Concurrent Updates of Shared Resource Counter

Le jeudi 7 juin 2012 09:53:48, Nir Zilberman a écrit : > Hi. > > We are handling multiple concurrent clients connecting to our system - > trying to get a license seat (each license has an initial capacity of > seats). We have a table which keeps count of the acquired seats for each > license. When

Re: [PERFORM] Tablespaces and query planning

> - The statistics gathered by ANALYZE are independent of the tablespace > containing the table. yes. > - The tablespace containing the table has no influence on query planning > unless seq_page_cost or random_page_cost has been set on the > tablespace. yes. > - VACUUM ANALYZE does the same

Re: [PERFORM] Seqscan slowness and stored procedures

Ivan Voras wrote: > I have a SQL function (which I've pasted below) and while testing its > code directly (outside a function), this is the "normal", default plan: > > http://explain.depesz.com/s/vfP (67 ms) > > and this is the plain with enable_seqscan turned off: > > http://explain.depesz.com/

[PERFORM] Tablespaces and query planning

Could somebody confirm or refute the following statements, please? - The statistics gathered by ANALYZE are independent of the tablespace containing the table. - The tablespace containing the table has no influence on query planning unless seq_page_cost or random_page_cost has been set on the