On 12/30/15 2:12 PM, Andy Colson wrote:
random_page_cost = 1
Humm, nope. I removed the config option, restart PG, then analyzed the
search table:
FYI, you can set that inside any session, any time you want. What's in
postgresql.conf is just the default value.
(For that matter, you can a
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 index scanning.
Hah. You
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 might be worthwhile to u
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, just in case there is
some
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 about this particular bu
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 something weird about th
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 you're using? What does
p
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 tsvector column?
Or maybe eve
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?
The table exists in a
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 named jasperia, I've bee
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
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 da
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 seem
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 time=4.996..4.996 rows=1 loo
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 the partial case actual
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 significantly more expensive th
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
explain analyze
select *
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 search_vec @@ to_tsquery
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 & ST & N');
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')
--
Seq Scan on search (c
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 yo
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')
> --
> Seq Scan on search (cost=0.00..2526.56 rows=1 width=6
On 12/29/15 5:21 PM, David G. Johnston wrote:
STABLE functions, nor VOLATILE ones, are candidates for indexing.
Only IMMUTABLE ones. The default for functions is VOLATILE.
I haven't the time to provide a solution to your problem - I'm just
pointing out "cannot get stable functi
On Tue, Dec 29, 2015 at 4:13 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Dec 29, 2015 at 3:52 PM, Andy Colson wrote:
>
>> [...]
>>
>> Originally it didn't have "STABLE STRICT", but I added it. Doesn't seem
>> to matter though. I cannot get this sql to use the index:
>
On Tue, Dec 29, 2015 at 3:52 PM, Andy Colson wrote:
> [...]
>
> Originally it didn't have "STABLE STRICT", but I added it. Doesn't seem
> to matter though. I cannot get this sql to use the index:
>
> explain analyze
> select *
> from search
> where search_vec @@ to_tsquery_partial('213 E 13 S
Hi all,
I seem to be missing something.
I'm using PG 9.3.9 on Slackware64.
My table:
create table search (
gid integer,
descr text,
search_vec tsvector
);
create index search_key on search using gin(search_vec);
I've put a bunch of data in it, and using to_tsquery use
26 matches
Mail list logo
