> On 14 Apr 2016, at 07:17, Adam Brusselback <adambrusselback@xxxxxxxxx> wrote: > > So fair enough, it does seem to be related to the lookup rather than maintenance on the index. I was misguided in my initial assumption. > > Spent quite a bit of time trying to come up with a self contained test, and it seems like I can't make it choose the GiST index unless I remove the regular btree index in my test case, though the opposite is true for my table in production. Not really sure what that means as far as what I need to do though. I've tried a vacuum full, analyze, rebuild index, drop and re-add the constraint... It still uses that GiST index for this query. > > Hell, a sequential scan is a ton faster even. > As i understand it, postgres needs a way to find rows for update. In explain analyze you provided, we see that it chose gist index for that. And that is a poor chose. I think you need a proper btree index for update query to work properly fast. Like index on (product_id, company_id, date_range) WHERE upper(price_generated_test.active_range) IS NULL. > On Wed, Apr 13, 2016 at 2:54 PM, Evgeniy Shishkin <itparanoia@xxxxxxxxx> wrote: > > > On 13 Apr 2016, at 20:14, Adam Brusselback <adambrusselback@xxxxxxxxx> wrote: > > > > Sorry, brain stopped working and I forgot to include the normal info. > > > > Postgres version: 9.5.1 > > Hardware: 2 core, 4gb Digital Ocean virtual server > > OS: Debian > > > > explain analyze for an example update: > > 'Update on price_generated (cost=32.45..644.83 rows=1 width=157) (actual time=29329.614..29329.614 rows=0 loops=1)' > > ' -> Nested Loop (cost=32.45..644.83 rows=1 width=157) (actual time=29329.608..29329.608 rows=0 loops=1)' > > ' -> HashAggregate (cost=32.04..34.35 rows=231 width=52) (actual time=1.137..2.090 rows=231 loops=1)' > > ' Group Key: pti.product_id, pti.company_id, pti.date_range' > > ' -> Seq Scan on _prices_to_insert pti (cost=0.00..30.31 rows=231 width=52) (actual time=0.060..0.678 rows=231 loops=1)' > > ' -> Index Scan using price_generated_company_product_date_active_excl on price_generated (cost=0.41..2.63 rows=1 width=151) (actual time=126.949..126.949 rows=0 loops=231)' > > ' Index Cond: (date_range = pti.date_range)' > > ' Filter: ((upper(active_range) IS NULL) AND (pti.product_id = product_id) AND (pti.company_id = company_id))' > > ' Rows Removed by Filter: 29460' > > 'Planning time: 3.134 ms' > > 'Execution time: 29406.717 ms' > > Well, you see execution time of 30 seconds because there are 231 index lookups, > each taking 126 ms. > > And that lookup is slow because of > Filter: ((upper(active_range) IS NULL) AND (pti.product_id = product_id) AND (pti.company_id = company_id))' > > Can you provide self-containing example of update? > I don't see there (upper(active_range) IS NULL condition is coming from. > > <excl constraint test case.sql> > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
