Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > No, that's not right, the table was empty. I rebuilt the table as it > was before, here are all three queries again: Ah, thanks for the more solid data. > -> Bitmap Index Scan on search_key (cost=0.00..63623.00 rows=1 width=0) > (actual

Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > Here are my results, if there are any others you'd like to see please > let me know. Thanks Tom. For comparison, could we see the results for the non-partial case, ie explain analyze select * from search where search_vec @@ to_tsquery('213 & E & 13

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 9:53 AM, Tom Lane wrote: Andy Colson writes: Here are my results, if there are any others you'd like to see please let me know. Thanks Tom. For comparison, could we see the results for the non-partial case, ie explain analyze select * from search where

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 9:55 AM, Andy Colson wrote: On 12/30/2015 9:53 AM, Tom Lane wrote: Andy Colson writes: Here are my results, if there are any others you'd like to see please let me know. Thanks Tom. For comparison, could we see the results for the non-partial case, ie

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 10:09 AM, Tom Lane wrote: Andy Colson writes: -> Bitmap Index Scan on search_key (cost=0.00..6.00 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1) Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text)) Hmm ... so

Re: [GENERAL] cannot get stable function to use index

I wrote: > This says there's only about a 25% runtime penalty for the partial match, > at least on your example, compared to the planner's estimate of 2700x > penalty :-(. Definitely need to fix that. I tried to reproduce this behavior with simple generated data, and could not: the estimates

Re: [GENERAL] Efficiently Triggering Autovacuum Analyze?

Cory Tucker writes: > This table is almost always queried using a combination of (account_id, > record_id) and is generally pretty fast. However, under certain loads, the > query becomes slower and slower as time goes on. The workload that causes > this to happen is when

Re: [GENERAL] Efficiently Triggering Autovacuum Analyze?

On Wed, Dec 30, 2015 at 11:20 AM Tom Lane wrote: > Cory Tucker writes: > > This table is almost always queried using a combination of (account_id, > > record_id) and is generally pretty fast. However, under certain loads, > the > > query becomes

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 1:55 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 1:07 PM, Tom Lane wrote: it seems like you've got some weird data statistics that are causing a misestimate. Could we see the pg_stats row for that tsvector column? Or maybe even the actual data?

Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > On 12/30/2015 1:55 PM, Tom Lane wrote: >> Are you using any nondefault planner settings? Anything else >> unusual about your installation? > There are others, but I'll bet its: > random_page_cost = 1 Nope... Maybe something weird about the build

Re: [GENERAL] cannot get stable function to use index

Wow thats bad. Here's another link: http://camavision.com/dn/stats.txt -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Efficiently Triggering Autovacuum Analyze?

We have a performance problem accessing one of our tables, I think because the statistics are out of date. The table is fairly large, on the order of 100M rows or so. The general structure of the table is as follows: Column | Type | Modifiers

Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > On 12/30/2015 1:07 PM, Tom Lane wrote: >> it seems like you've got some weird data statistics that are causing a >> misestimate. Could we see the pg_stats row for that tsvector column? >> Or maybe even the actual data? > The table exists in a schema

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:03 PM, Andy Colson wrote: On 12/30/2015 1:55 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 1:07 PM, Tom Lane wrote: it seems like you've got some weird data statistics that are causing a misestimate. Could we see the pg_stats row for that

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:18 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 1:55 PM, Tom Lane wrote: Are you using any nondefault planner settings? Anything else unusual about your installation? There are others, but I'll bet its: random_page_cost = 1 Nope... Maybe

Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > On 12/30/2015 2:18 PM, Tom Lane wrote: >> Maybe something weird about the build you're using? What does >> pg_config print? > [ output ] No smoking gun there either. It might be worthwhile to update to 9.3.10, just in case there is something wonky

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:33 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 2:18 PM, Tom Lane wrote: Maybe something weird about the build you're using? What does pg_config print? [ output ] No smoking gun there either. It might be worthwhile to update to 9.3.10,

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:39 PM, Andy Colson wrote: On 12/30/2015 2:33 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 2:18 PM, Tom Lane wrote: Maybe something weird about the build you're using? What does pg_config print? [ output ] No smoking gun there either. It

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 1:07 PM, Tom Lane wrote: I wrote: This says there's only about a 25% runtime penalty for the partial match, at least on your example, compared to the planner's estimate of 2700x penalty :-(. Definitely need to fix that. I tried to reproduce this behavior with simple generated

Re: [GENERAL] Efficiently Triggering Autovacuum Analyze?

On 12/30/2015 11:09 AM, Cory Tucker wrote: > We have a performance problem accessing one of our tables, I think > because the statistics are out of date. The table is fairly large, on > the order of 100M rows or so. > The fix I have employed to restore the speed of the query after I notice > it

Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > Ok, I can reproduce this now. The full vacuum analyze isn't needed. > If I drop and recreate the table it goes back to preferring table scan. > I can "analyze search" and it still table scans. > But once I "vacuum analyze search", then it starts

Re: [GENERAL] cannot get stable function to use index

Andy Colson writes: > -> Bitmap Index Scan on search_key (cost=0.00..6.00 rows=1 width=0) > (actual time=0.025..0.025 rows=0 loops=1) > Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & > N'::text)) Hmm ... so the partial case actually is

Re: [GENERAL] Transfer db from one port to another

Killian Driscoll wrote: > It worked - thank you very much for your time. Great! > Regarding the file format used: I had used the pg_dump with .sql, but you > suggested .out. Is there a particular reason to use .out instead of .sql when > backing up? No, doesn't

Re: [GENERAL] Transfer db from one port to another

On 12/29/2015 11:38 PM, Killian Driscoll wrote: On 24 December 2015 at 18:33, Adrian Klaver > wrote: It worked - thank you very much for your time. Regarding the file format used: I had used the pg_dump with .sql, but you

Re: [GENERAL] cannot get stable function to use index

On 12/29/2015 6:03 PM, Jim Nasby wrote: If I'm reading EXPLAIN ANALYZE correctly, to_tsquery_partial is being simplified out of the query entirely: Filter: (search_vec @@ to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) || ':*'::text))) Part of this could well be that

Re: [GENERAL] cannot get stable function to use index

On 12/29/2015 6:35 PM, Tom Lane wrote: Andy Colson writes: I cannot get this sql to use the index: explain analyze select * from search where search_vec @@ to_tsquery_partial('213 E 13 ST N') --