61 reputation
15
bio website
location
age
visits member for 2 years, 3 months
seen Jul 24 '13 at 14:02

May
19
awarded  Notable Question
Jul
11
awarded  Popular Question
Jun
12
awarded  Popular Question
Oct
31
awarded  Supporter
Oct
31
revised PostgreSQL Index optimization with dates
added 591 characters in body
Oct
31
comment PostgreSQL Index optimization with dates
With cluster, performance is 10x better. See results above (update of main question). I'll have extended tests to perform to verify other impacts
Oct
30
comment PostgreSQL Index optimization with dates
And for now, Heroku doesn't support 9.2 yet. I can upgrade to 9.1 only.
Oct
30
comment PostgreSQL Index optimization with dates
See update for query analysis output link. I'll try the CLUSTER. Yes I'll flag for dba.SE
Oct
30
comment PostgreSQL Index optimization with dates
So far so good ! With Partial index : 73861.047ms I'll continue the CLUSTER test later on. Thanks
Oct
29
comment PostgreSQL Index optimization with dates
The table structure has ~20-30 columns, a few foreign keys integers, string, text, booleans. The index definition is in the posting above next to INDEX USEd. (I posted the 8 indexes used by the queries). I have a few more indexes for quicker update and selects for my application. Finally, I am using a cloud DB, and I haven't change anything. I am wondering mostly if my index definition is as good as it can gets before getting to those optimization. Nevertheless, I'll update with the info.
Oct
29
asked PostgreSQL Index optimization with dates
Apr
14
comment PostgreSQL - Help Optimizing SQL performance - full text search
Caching comes in the way... it roughly gives a performance as bad. 10secs 1500 rows. I understand your explanation. It makes sense. But while doing a text search.... any way to build my index for proper quality sorting without extracting everything?
Apr
13
revised PostgreSQL - Help Optimizing SQL performance - full text search
added 3248 characters in body
Apr
13
awarded  Student
Apr
13
comment PostgreSQL - Help Optimizing SQL performance - full text search
Increased from 100MB to 650MB, gave no performance difference. (work_mem)
Apr
13
revised PostgreSQL - Help Optimizing SQL performance - full text search
added 1369 characters in body
Apr
13
awarded  Editor
Apr
13
revised PostgreSQL - Help Optimizing SQL performance - full text search
added 1369 characters in body
Apr
13
comment PostgreSQL - Help Optimizing SQL performance - full text search
Here's another EXPLAIN ANALYZE : (see edit in my above post)
Apr
12
asked PostgreSQL - Help Optimizing SQL performance - full text search