Re: [GENERAL] Slow query plan used

2017-06-01 Thread Tom Lane
"Wetzel, Juergen (Juergen)" writes: > Tom Lane writes: >> You might get some traction by creating indexes on lower(searchfield1) etc. > I will try that. Does that mean the column statistics will only be collected > when there's an index on the table/column? No; ANALYZE collects stats on plain

Re: [GENERAL] Slow query plan used

2017-06-01 Thread Wetzel, Juergen (Juergen)
Andreas Kretschmer writes: > please consider my plan B) and increase the stats. See my other mail. I tried that also. Combined with the partial index. But still same result. Bill Moran writes: > LIKE queries are probably challenging to plan, especially when they're > not > left-anchored: how c

Re: [GENERAL] Slow query plan used

2017-06-01 Thread Tom Lane
Bill Moran writes: > LIKE queries are probably challenging to plan, especially when they're not > left-anchored: how can the planner be reasonalbly expected to estimate how > many rows will be matched by a given LIKE expression. Yeah, especially without any statistics. The core problem here appe

Re: [GENERAL] Slow query plan used

2017-06-01 Thread Bill Moran
On Thu, 1 Jun 2017 16:45:17 +0200 Andreas Kretschmer wrote: > > Am 01.06.2017 um 14:07 schrieb Wetzel, Juergen (Juergen): > > > > Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen): > Only 130 rows out of the 3 have ARCHIVED = 0 > >>> in this case i would suggest a partial index: >

Re: [GENERAL] Slow query plan used

2017-06-01 Thread Andreas Kretschmer
Am 01.06.2017 um 14:07 schrieb Wetzel, Juergen (Juergen): Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen): Only 130 rows out of the 3 have ARCHIVED = 0 in this case i would suggest a partial index: create index on (archived) where archived = 0; Thanks, Andreas. Sorry for the

Re: [GENERAL] Slow query plan used

2017-06-01 Thread Wetzel, Juergen (Juergen)
Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen): >>> Only 130 rows out of the 3 have ARCHIVED = 0 >> in this case i would suggest a partial index: >> create index on (archived) where archived = 0; > Thanks, Andreas. > > Sorry for the confusion about the table names. > The hint with

Re: [GENERAL] Slow query plan used

2017-05-31 Thread Andreas Kretschmer
Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen): Only 130 rows out of the 3 have ARCHIVED = 0 in this case i would suggest a partial index: create index on (archived) where archived = 0; Thanks, Andreas. Sorry for the confusion about the table names. The hint with the partial

Re: [GENERAL] Slow query plan used

2017-05-31 Thread Wetzel, Juergen (Juergen)
>> Only 130 rows out of the 3 have ARCHIVED = 0 > in this case i would suggest a partial index: > create index on (archived) where archived = 0; Thanks, Andreas. Sorry for the confusion about the table names. The hint with the partial index sounds as it could solve the problem. I will tes

Re: [GENERAL] Slow query plan used

2017-05-31 Thread Andreas Kretschmer
Am 30.05.2017 um 10:42 schrieb Wetzel, Juergen (Juergen): > I have a question concerning the query planner. I observe that chosen query plan differs on length and content of a like > search expression. We have a view combining data from two tables, both containing same number of rows (round a

[GENERAL] Slow query plan used

2017-05-30 Thread Wetzel, Juergen (Juergen)
I have a question concerning the query planner. I observe that chosen query plan differs on length and content of a like search expression. We have a view combining data from two tables, both containing same number of rows (round about 3). Used PostgreSQL version is 9.3.15 on Windows. DDL of