Re: [PERFORM] Inefficient filter order in query plan

2014-02-27 Thread Tom Lane
Tom Coogan writes: > Thanks Tom but why would strict equality checking (e.g. model = > 'User') have the same cost as LIKE operations which (may) have to do > pattern matching? A bit of consultation of pg_proc.procost will show you that just about the only internal functions with costs different f

Re: [PERFORM] Inefficient filter order in query plan

2014-02-27 Thread Tom Coogan
On Thu, Feb 27, 2014 at 12:04 PM, Tom Lane wrote: > > It doesn't know that LIKE is any more expensive than the other operators, > so there's no reason to do them in any particular order. > Thanks Tom but why would strict equality checking (e.g. model = 'User') have the same cost as LIKE operation

Re: [PERFORM] Inefficient filter order in query plan

2014-02-27 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> You could try increasing the cost attributed to the texticlike() function >> if you don't like the results you're getting here. > Perhaps we should be attributing some additional cost to operations > which (are likely to) require d

Re: [PERFORM] Inefficient filter order in query plan

2014-02-27 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Tom Coogan writes: > > I'd like to understand why PostgreSQL is choosing to filter on the most > > inefficient predicate first in the query below. > > It doesn't know that LIKE is any more expensive than the other operators, > so there's no reason to do th

Re: [PERFORM] Inefficient filter order in query plan

2014-02-27 Thread Tom Lane
Tom Coogan writes: > I'd like to understand why PostgreSQL is choosing to filter on the most > inefficient predicate first in the query below. It doesn't know that LIKE is any more expensive than the other operators, so there's no reason to do them in any particular order. You could try increasi

[PERFORM] Inefficient filter order in query plan

2014-02-27 Thread Tom Coogan
I'd like to understand why PostgreSQL is choosing to filter on the most inefficient predicate first in the query below. Version: PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 64-bit Hardware: 24 2.93GHz Xeon cores and 32

Re: [PERFORM] not using my GIN index in JOIN expression

2014-02-27 Thread Jean-Max Reymond
Le 27/02/2014 15:19, Heikki Linnakangas a écrit : On 02/27/2014 04:06 PM, Jean-Max Reymond wrote: I am running the last version of PostgreSQL 9.3.3 I have two tables detm and corm and a lot of datas in the column cormdata of corm table (1.4 GB). I have a GIN index on cormdata: CREATE INDEX ix_c

Re: [PERFORM] not using my GIN index in JOIN expression

2014-02-27 Thread Heikki Linnakangas
On 02/27/2014 04:06 PM, Jean-Max Reymond wrote: I am running the last version of PostgreSQL 9.3.3 I have two tables detm and corm and a lot of datas in the column cormdata of corm table (1.4 GB). I have a GIN index on cormdata: CREATE INDEX ix_corm_fulltext_cormdata ON corm USING gin (to_ts

[PERFORM] not using my GIN index in JOIN expression

2014-02-27 Thread Jean-Max Reymond
I am running the last version of PostgreSQL 9.3.3 I have two tables detm and corm and a lot of datas in the column cormdata of corm table (1.4 GB). I have a GIN index on cormdata: CREATE INDEX ix_corm_fulltext_cormdata ON corm USING gin (to_tsvector('french'::regconfig, cormdata)) WHERE co

Re: [PERFORM] Query taking long time

2014-02-27 Thread acanada
Hello, I have changed the multicolumn index from: "entity2document_name_qualifier_index" btree (name, qualifier) to: "document_qualifier_name_index" btree (qualifier, name) And now the planner doesn't "Recheck cond:" (there are only three different qualifiers vs. millions of n