Thread: cannot get stable function to use index
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 uses the index fine:
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
QUERY PLAN
-----------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=16.00..17.02 rows=1 width=69)
(actual time=87.493..87.494 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
-> Bitmap Index Scan on search_key (cost=0.00..16.00 rows=1
width=0) (actual time=87.478..87.478 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N'::text))
Total runtime: 87.554 ms
(5 rows)
Here is the problem, I'd like to use this function from
http://workshops.boundlessgeo.com/tutorial-autocomplete/
CREATE OR REPLACE FUNCTION public.to_tsquery_partial(text)
RETURNS tsquery AS $$
SELECT to_tsquery(
array_to_string(
regexp_split_to_array(
trim($1),E'\\s+'),' & ') ||
CASE WHEN $1 ~ ' $' THEN '' ELSE ':*' END)
$$ LANGUAGE 'sql' STABLE STRICT;
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 ST N')
--------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2526.56 rows=1 width=69) (actual
time=68.033..677.490 rows=1 loops=1)
Filter: (search_vec @@
to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
':*'::text)))
Rows Removed by Filter: 76427
Total runtime: 677.548 ms
(4 rows)
to_tsquery_partial() calls to_tsquery() and array_to_string(), both of
which I checked, and all of them are marked as stable.
Any hints why this is happening?
Thanks,
-Andy
[...]
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 ST N')
--------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2526.56 rows=1 width=69) (actual time=68.033..677.490 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) || ':*'::text)))
Rows Removed by Filter: 76427
Total runtime: 677.548 ms
(4 rows)
to_tsquery_partial() calls to_tsquery() and array_to_string(), both of which I checked, and all of them are marked as stable.
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 function to use index" is working as designed and as is logically required. An index must not rely upon outside information, most typically time, since there exists no means for an index to update itself based upon changes in the environment. The only type of function guaranteed to not rely upon the external environment is an immutable one. And no, you shouldn't lie by marking a function immutable to get this to work. The system does not check that the stated volatility and the actual implementation match.
David J.
[...]
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 ST N')
--------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2526.56 rows=1 width=69) (actual time=68.033..677.490 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) || ':*'::text)))
Rows Removed by Filter: 76427
Total runtime: 677.548 ms
(4 rows)
to_tsquery_partial() calls to_tsquery() and array_to_string(), both of which I checked, and all of them are marked as stable.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 function to use index" is working as designed and as is logically required. An index must not rely upon outside information, most typically time, since there exists no means for an index to update itself based upon changes in the environment. The only type of function guaranteed to not rely upon the external environment is an immutable one. And no, you shouldn't lie by marking a function immutable to get this to work. The system does not check that the stated volatility and the actual implementation match.
So while the above is all true I apparently mis-understood your question... :(
I'm going to wait for someone thinking more clearly to answer...but it seems that given an inability to prove that the result of the function call is meaningfully selective the system would default to choosing a sequential scan plan over an index. You happen to choose a value that only returns a single row but nothing prevents you from picking one that returns the entire table. There may be other factors involved as I am not that familiar with the full text search capabilities of PostgreSQL.
David J.
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 function to use index" is working as
> designed and as is logically required. An index must not rely upon
> outside information, most typically time, since there exists no
> means for an index to update itself based upon changes in the
> environment. The only type of function guaranteed to not rely upon
> the external environment is an immutable one. And no, you shouldn't
> lie by marking a function immutable to get this to work. The system
> does not check that the stated volatility and the actual
> implementation match.
>
>
> So while the above is all true I apparently mis-understood your
> question... :(
I'm not an expert on text search, but presumably the tsvector knows what
config was used, which means as long as the config is part of the index
it should be OK.
Marking to_tsquery_partial as immutable would be wrong and potentially
dangerous, as you can change default_text_search_config at any time,
which would change the output of to_tsquery (as well as to_tsvector).
> I'm going to wait for someone thinking more clearly to answer...but it
> seems that given an inability to prove that the result of the function
> call is meaningfully selective the system would default to choosing a
> sequential scan plan over an index. You happen to choose a value that
> only returns a single row but nothing prevents you from picking one that
> returns the entire table. There may be other factors involved as I am
> not that familiar with the full text search capabilities of PostgreSQL.
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 you're not feeding the same data to
to_tsquery. Your hard-coded example is
where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
but your second query becomes '213 & E & 13 & ST & N:*'. Have you tried
that as a hard-coded value?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
Andy Colson <andy@squeakycode.net> 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=69) (actual
> time=68.033..677.490 rows=1 loops=1)
> Filter: (search_vec @@
> to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
> ':*'::text)))
> Rows Removed by Filter: 76427
> Total runtime: 677.548 ms
> (4 rows)
If you force it with enable_seqscan = off, you'll soon see that it's
capable of picking the indexscan plan, but it doesn't want to because it
estimates that the cost will be much higher, which seems to be a
consequence of the ":*" in the query. (Even though the functions involved
are only stable, the planner is capable of seeing through them to look at
the pattern that will be fed to the GIN index search.) You get the same
results if you use the resulting tsquery without any function at all.
For example (with dummy data), I get
regression=# explain select * from search
where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
QUERY PLAN
----------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..3774.01 rows=1 width=21)
Filter: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
(2 rows)
regression=# set enable_seqscan TO 0;
SET
regression=# explain select * from search
where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
QUERY PLAN
--------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=104444.00..104448.01 rows=1 width=21)
Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
-> Bitmap Index Scan on search_key (cost=0.00..104444.00 rows=1 width=0)
Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
(4 rows)
but for comparison, with a pattern without ':*', I get
regression=# explain select * from search
where search_vec @@ '213 & e & 13 & st & n'::tsquery;
QUERY PLAN
------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=44.00..48.01 rows=1 width=21)
Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
-> Bitmap Index Scan on search_key (cost=0.00..44.00 rows=1 width=0)
Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
(4 rows)
I'm inclined to think this is a bug in the estimator; it seems to be
charging for many more "entry page" fetches than there are pages in
the index. But maybe it's right and there will be lots of repeated
work involved. It would be interesting to see EXPLAIN ANALYZE results
from your data for these examples.
regards, tom lane
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 you're not feeding the same data to
> to_tsquery. Your hard-coded example is
>
> where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
>
> but your second query becomes '213 & E & 13 & ST & N:*'. Have you tried
> that as a hard-coded value?
Ahh! Yep, that was the missing link. Jeez, I can't believe I couldn't
find it. The :* is for matching partials, its even IN the name
to_tsquery_partial.
Indeed, this does not use an index:
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*')
Thank you!
-Andy
On 12/29/2015 6:35 PM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.net> 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=69) (actual
>> time=68.033..677.490 rows=1 loops=1)
>> Filter: (search_vec @@
>> to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
>> ':*'::text)))
>> Rows Removed by Filter: 76427
>> Total runtime: 677.548 ms
>> (4 rows)
>
> If you force it with enable_seqscan = off, you'll soon see that it's
> capable of picking the indexscan plan, but it doesn't want to because it
> estimates that the cost will be much higher, which seems to be a
> consequence of the ":*" in the query. (Even though the functions involved
> are only stable, the planner is capable of seeing through them to look at
> the pattern that will be fed to the GIN index search.) You get the same
> results if you use the resulting tsquery without any function at all.
> For example (with dummy data), I get
>
> regression=# explain select * from search
> where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
> QUERY PLAN
> ----------------------------------------------------------------------------------
> Seq Scan on search (cost=0.00..3774.01 rows=1 width=21)
> Filter: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
> (2 rows)
>
> regression=# set enable_seqscan TO 0;
> SET
> regression=# explain select * from search
> where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
> QUERY PLAN
> --------------------------------------------------------------------------------------------
> Bitmap Heap Scan on search (cost=104444.00..104448.01 rows=1 width=21)
> Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
> -> Bitmap Index Scan on search_key (cost=0.00..104444.00 rows=1 width=0)
> Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
> (4 rows)
>
> but for comparison, with a pattern without ':*', I get
>
> regression=# explain select * from search
> where search_vec @@ '213 & e & 13 & st & n'::tsquery;
> QUERY PLAN
> ------------------------------------------------------------------------------------------
> Bitmap Heap Scan on search (cost=44.00..48.01 rows=1 width=21)
> Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
> -> Bitmap Index Scan on search_key (cost=0.00..44.00 rows=1 width=0)
> Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
> (4 rows)
>
> I'm inclined to think this is a bug in the estimator; it seems to be
> charging for many more "entry page" fetches than there are pages in
> the index. But maybe it's right and there will be lots of repeated
> work involved. It would be interesting to see EXPLAIN ANALYZE results
> from your data for these examples.
>
> regards, tom lane
>
Here are my results, if there are any others you'd like to see please
let me know. Thanks Tom.
# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2144.42 rows=1 width=69) (actual
time=30.584..361.147 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
Rows Removed by Filter: 76427
Total runtime: 361.181 ms
(4 rows)
Time: 363.012 ms
# set enable_seqscan TO 0;
SET
Time: 0.185 ms
# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=63716.00..63717.02 rows=1 width=69)
(actual time=4.354..4.355 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
-> Bitmap Index Scan on search_key (cost=0.00..63716.00 rows=1
width=0) (actual time=4.351..4.351 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
Total runtime: 4.370 ms
(5 rows)
Time: 4.794 ms
Andy Colson <andy@squeakycode.net> 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');
regards, tom lane
On 12/30/2015 9:53 AM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.net> 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');
>
> regards, tom lane
>
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=6.00..7.02 rows=1 width=100) (actual
time=0.029..0.029 rows=0 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
-> 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))
Total runtime: 0.060 ms
(5 rows)
On 12/30/2015 9:55 AM, Andy Colson wrote:
> On 12/30/2015 9:53 AM, Tom Lane wrote:
>> Andy Colson <andy@squeakycode.net> 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');
>>
>> regards, tom lane
>>
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------
>
> Bitmap Heap Scan on search (cost=6.00..7.02 rows=1 width=100) (actual
> time=0.029..0.029 rows=0 loops=1)
> Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
> -> 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))
> Total runtime: 0.060 ms
> (5 rows)
>
>
>
>
Oh! I just realized... this is on my test box and I just blew away that
table to rebuild it. And I have to run off to a meeting, so if this
analyze makes no sense, that's why.
I'll re-re-do it within the hour. Sorry about that.
-Andy
Andy Colson <andy@squeakycode.net> 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 than
the non-partial case: 4 msec vs .025 msec. Still, that's about a 200x
penalty, not the 10000x penalty the planner is ascribing to it.
Thanks for the data! I'll go moan about this on -hackers.
regards, tom lane
On 12/30/2015 10:09 AM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.net> 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 than
> the non-partial case: 4 msec vs .025 msec. Still, that's about a 200x
> penalty, not the 10000x penalty the planner is ascribing to it.
>
> Thanks for the data! I'll go moan about this on -hackers.
>
> regards, tom lane
>
No, that's not right, the table was empty. I rebuilt the table as it
was before, here are all three queries again:
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2211.08 rows=1 width=73) (actual
time=31.904..380.568 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
Rows Removed by Filter: 79071
Total runtime: 380.609 ms
set enable_seqscan TO 0;
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=63623.00..63624.02 rows=1 width=73)
(actual time=5.004..5.004 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
-> Bitmap Index Scan on search_key (cost=0.00..63623.00 rows=1
width=0) (actual time=4.996..4.996 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
Total runtime: 5.045 ms
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=23.00..24.02 rows=1 width=73)
(actual time=4.067..4.067 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
-> Bitmap Index Scan on search_key (cost=0.00..23.00 rows=1
width=0) (actual time=4.057..4.057 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N'::text))
Total runtime: 4.109 ms
Andy Colson <andy@squeakycode.net> 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
loops=1)
> Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
> -> Bitmap Index Scan on search_key (cost=0.00..23.00 rows=1 width=0) (actual time=4.057..4.057 rows=1 loops=1)
> Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
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.
regards, tom lane
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 to track the actual cost reasonably well. So
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?
regards, tom lane
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 data, and could > not: the estimates seem to track the actual cost reasonably well. So > 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? > > regards, tom lane > The table exists in a schema named jasperia, I've been removing the name for simplicity. The dump of the table is here: http://camavision.com/dn/search.sql.bz2 Here is the pg_stats, I hope word wrap doesn't mess this up too bad: schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram ------------+-----------+------------+-----------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------- jasperia | search | gid | f | 0 | 4 | -0.364807 | {152772,155637,155982,156520,157031,160805,165284,165865,172081,172100,172238,172373,172399,172412,172623,172727,176093,176096,176127,176146,176159,176162,176171,176185,176213,176236,176286,176390,176421,176635,176642,176668,176670,176859,177049,177071,177261,177306,177334,177359,178054,178097,178208,178382,178483,149328,149364,149384,149387,149392,149410,149425,149437,149438,149444,149472,149474,149476,149489,149493,149500,149540,149543,149549,149555,149571,149582,149585,149592,149617,149620,149658,149666,149675,149715,149719,149745,149783,149801,149818,149820,149842,149860,149894,149912,149920,149927,149941,149955,149957,150044,150067,150068,150079,150109,150126,150145,150156,150187,150216} | {0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001} | {149330,149636,149928,150207,150470,150722,150980,151230,151517,151759,152008,152246,152495,152763,153013,153280,153550,153806,154074,154334,154595,154866,155125,155394,155658,155919,156213,156513,156783,157054,157325,157579,157837,158127,158392,158654,158929,159211,159554,159876,160225,160554,160880,161218,161563,161917,162270,162646,162989,163307,163678,164067,164404,164719,165042,165355,165695,166049,166382,166726,167054,167377,167720,168045,168367,168728,169017,169314,169668,169990,170304,170622,170948,171276,171600,171944,172231,172481,172748,173089,173434,173780,174112,174437,174768,175107,175439,175813,176089,176312,176528,176747,176956,177182,177445,177716,178008,178263,178569,178870,179207} | 0.300417 | | | jasperia | search | type | f | 0 | 4 | 5 | {deed,pin,add,dba,cont} | {0.380367,0.371067,0.212667,0.0239667,0.0119333} | | 0.904334 | | | jasperia | search | descr | f | 0 | 20 | -0.647676 | {"CITY OF NEWTON","IOWA INTERSTATE RAILROAD LTD","U S FISH & WILDLIFE SERVICE","TALSMA LAND HOLDINGS LLLP","JASPER COUNTY","FARRELL PROPERTIES INC",CONVERSION,"STATE OF IOWA","WINCHESTER CARRIAGE INC","WILSON AGRI INC","IOWA NATURAL HERITAGE FOUNDATION","ROCK CREEK STATE PARK","CITY OF PRAIRIE CITY","JASPER COUNTY CONSERVATION BOARD","VACANT LOT","VERMEER FARMS INC,% VERMEER MFG","WEHRLE FARMS LLC","BIRKENHOLTZ, LARRY D & JONE M TRUSTS-TRUSTEES","CENTRALLY ASSESSED","HEALY FARM TRUST %VANZEE FARM MGMT CO","JANSEN, JERRY G & LINDA D REV TRUST/TRUSTEES","ROORDA FAMILY FARMS LLC","BARTELMA FARMS INC","BIRKENHOLTZ, LARRY D TRUST ETAL","BRUMMEL, BRUCE L & LINDA L","CITY OF COLFAX","CLEVENGER PROPERTIES LLC","PCW FARMLAND LLC","STATE OF IOWA IOWA DEPT OF NATURAL RESOURCES","SNOOK, W T INC","BROWN, MERL G REV TRUST/TRUSTEE 1/2 & GERDENA M REV TRUST/TRUSTEE 1/2","CHICHAQUA BIKE TRAIL","ELLIOTT, GUY","LAPLANT, JAMES R & CAROL A","LOUDEN FARMS INC","MORRIS, JERRY REV TRUST/TRUSTEE","PRENDERGAST, DAVID J REV TRST TRSTEE 1/2 & LILLIAN REV TRST TRSTEE 1/2","VANSLAND LTD","CENTRAL IOWA WATER ASSOCIATION","CITY OF KELLOGG","CITY OF MONROE","GANNON FARM INC","MARTIN MARIETTA MATERIALS INC","MERCY PROFESSIONAL PRACTICE ASSOCIATES INC","RO-DEN-CRIS INC","RUNNER, BEATRICE D LE & HUTCHESON, JANET K ETAL RMDR","SCHNELL FARMS INC,% SCHNELL, DAVID","BIRKENHOLTZ, LARRY D TRUST & JONE TRUST","BRUMMEL, BRUCE & LINDA (JT)","DEGREEF, FREDA L LE 1/2 & DEGREEF, FREDA L 1/2","DITTMER, JEREMY J & JACQUELYN J (JT)","HANSON, NOLA & VANGINKEL, MARGARET E REV TRUST","LUST, JANET S LIVING TRUST/TRUSTEE & LUST CARD D TRUSTEE","MAGGARD, MAX E & GLORIA JOYCE REV TRUST","O'ROAKE, CHARLES J & THERESA M","PIERCE, IRENE I REV TRUST","POAGE, WAYNE C & IRMA C REVOCABLE TRUST/TRUSTEES","RUMBAUGH, CARROLL & JUDY","SHARP FARMS INC","WORMLEY, JACK M & CHLORESSA DEW TRUSTS","2201 RUSTY WALLACE DR","BAXTER ECONOMIC DEVELOPMENT CORPORATION","BUCK CREEK FARMS PRTSHP LLP","FATLAND, JOHN L TRUST/TRUSTEE & DONITA V TRUST/TRUSTEE","GANNON, WILLIAM J & KATHLEEN KENNEDY","GUTHRIE, ILLA G REV TRUST","JANSCO FARMS INC","KADING PROPERTIES LLC","LIVINGSTON, JEAN ANN","STATE OF IOWA IOWA DEPT OF NATURAL RESOURCES(REAP)","STEENHOEK, HARLEY H & PHILLIS B REV TRUST/TRUSTEES","VERSENDAAL, DEXTER B & TERESA K (JT)","WILSON, SHERYL V REV TRUST","ASHBY, ROBERT W-MARY MARGARET REVOCABLE TRUST-TRUSTEES","BACKUS, KAREN S","BLOM, ALLEN LEROY 1/2 & BLOM, GREGORY ALLEN & ETALS 1/2","CITY OF BAXTER","CITY OF NEWTON IOWA","CRH PROPERTIES LLC","FLOSS FARMS INC","HANSON, SHIRLEY LADD REV TRUST/TRUSTEE","HEARTLAND COOP","KJ FARMS LLC","MCKLVEEN, J H & COMPANY","NEARMYER, RICHARD L",PARSONAGE,"ROUSH, ELAINE M & ROUSH, ELAINE M LE & ROUSH, HAROLD JAY 1/2 OF LE & ETAL","TAYLOR FARMS LLC","TRI-LEONARD FARMS LLC",VACANT,"VANHEMERT, KEITH","VANROEKEL & SONS INC","VANSICE, CHARLES R & LINDA S","VOS, MELDON L & ELOISE J","WEBB, PHILIP F","ARCHER PROPERTIES INC","BEALS, LARRY J FAMILY TRUST 1/2 BEALS, DONNA R 1/2","BEATTY, ALICE T","BEYER, DENNIS L","BRANDHOF, JOHN EDWARD & LEONA W REV TRUST/TRUSTEES"} | {0.00213333,0.00206667,0.0017,0.00166667,0.00143333,0.0009,0.000766667,0.000766667,0.000766667,0.0007,0.000666667,0.000666667,0.0006,0.0006,0.0006,0.0006,0.0006,0.000566667,0.000533333,0.000533333,0.000533333,0.000533333,0.000466667,0.000466667,0.000466667,0.000466667,0.000466667,0.000466667,0.000466667,0.000433333,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.0004,0.000366667,0.000366667,0.000366667,0.000366667,0.000366667,0.000366667,0.000366667,0.000366667,0.000366667,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.000333333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000 233333,0.000233333,0.000233333,0.000233333,0.000233333} | {"",01.33.400.002,02.14.380.010,02.34.300.002,03.33.300.009,05.03.300.004,06.03.301.028,06.22.100.005,07.18.300.004,08.15.100.009,08.26.331.013,08.27.327.007,08.28.401.033,08.33.176.006,08.33.429.011,08.34.180.013,08.34.304.036,08.35.201.001,08.35.406.007,09.22.200.007,"10 VALLEY VIEW DR",10.22.301.002,"101 1 AVE W","10532 W 76 ST N",11.01.379.001,11.12.151.009,11.35.300.901,"11321 HWY S-62 N",12.07.400.008,"1208 W 9 ST S",13.03.301.005,13.13.100.006,"1308 S 8 AVE E",14.16.300.008,"1412 W 28 ST N",15.18.400.001,16.01.103.004,16.04.400.001,"1609 MONROE DR",17.25.454.003,17.36.404.003,18.19.100.007,"1807 S 4 AVE E",19.29.300.001,20.08.137.002,20.14.300.002,"2020 1 AVE E","2136A E 60 ST S","2609 HWY S-74 S","307 E STATION ST","323 BROAD ST","404 KAYLA LANE","4254 JEWEL ST","504 E 20 ST S","5309 E 5 ST S","605 W SHERMAN","6401 W 140 ST S","710 E 13 ST N","7793 HWY 6 E","826 S 5 AVE W","913 S 8 AVE W","AIKIN, KENDALL D","BABCOCK, MARK O,& DIANE R","BELL, ROBERT F,& VIRGINIA","BOOT, BRIAN & ETALS","BUCK CREEK FARMS PARTNERSHIP LLP","CDC OF JASPER COUNTY INC","COCHRANE, JUSTIN & BRIDGET (JT)","CW GRIFFITH PROPERTIES LLC","DEROUCHEY, DAVID,& ANNETTE","DURR, JAMES WILLIAM","ESTANCIA VIDA BUENA LLC","FORTUNE, DONALD JR,& GWENDOLYN","GOOCH, DAVID R,& JOAN K","HANEL FARMS LLC","HERREMA, DENNIS J & PAM S","HUGEN, WILBUR B REV TRUST, HUGEN, WILLIAM WAYNE TRUSTEE","JANSEN, DONALD,& KAREN","KALDENBERG, ALICIA M","KRAMER, STEVE ALLEN & KATIE JEANNE (JT)","LEWIS, MARTY L,& JOYCE A","MAHL, TIMOTHY SCOTT,& AMY","MCILRATH, ALICE L","MILLIGAN, MARK D & CHRISTIE D","NEARMYER FARM CO","OKLAND ACRES GOLF CLUB","PESKA, KEVIN,& TONI","RABEDEAUX, CYNTHIA D LIVING TRUST/TRUSTEE & STEVEN G TRUSTEE","ROGAHN, MATTHEW G","SANDQUIST, WILLIAM & COLEEN","SHARPNACK, THOMAS","SOLID ROCK CHURCH","STROVERS, MARILYN R REV TRUST","THOMAS, CINDY L","UNITED METHODIST CHURCH","VANGENDEREN, RONALD L,& LOIS VIRGINIA","VANWYK, DIRK & JOURDAN","VERSTEEGT, AMY H 1/2 INT LE 1/2 INT RMDR ETAL","WATTS, BENJAMIN DANIEL","WILSON, HAROLD T,& PAULA","ZYZANSKI, STEPHEN J TRUST-TRUSTEE"} | -0.382801 | | | jasperia | search | search_vec | f | 0 | 45 | -1 | | | | | {1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u,v,w,z,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,32,35,36,39,44,48,51,52,59,60,62,64,65,67,68,75,76,80,83,84,88,92,95,99,av,co,ct,dr,ii,jo,jr,jt,lc,le,ln,lp,of,op,pl,pr,rd,ro,sr,st,vw,-12,-17,-33,-36,-38,-48,-52,-62,-70,-74,1/2,1/3,1/4,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,120,121,124,125,128,132,140,142,148,156,1st,2/3,200,201,202,203,204,205,206,207,208,209,210,211,213,214,222,224,2nd,3/4,300,301,302,303,304,305,306,307,308,309,310,312,313,314,315,317,321,3rd,400,401,402,403,404,405,406,407,408,409,410,411,415,421,4th,500,501,502,503,504,505,506,507,508,510,513,516,5th,600,601,602,603,604,605,606,607,608,609,610,611,615,620,700,701,702,703,704,705,706,707,708,709,710,711,712,715,720,800,801,802,807,8th,901,acr,ami,and,ann,cir,cox,ctr,dan,dee,den,don,dop,est,fay,guy,hay,hwi,iii,inc,int,jay,jen,joe,jon,joy,kay,lee,leo,llc,llp,loi,lot,lou,ltd,mae,mat,max,mfg,oak,pcw,rae,ray,res, rev,rex,roy,son,sta,sue,the,tom,trl,vos,way,1000,1101,2201,adam,agri,alan,alic,anna,area,ball,bank,barr,beal,bell,beth,bike,blom,bort,buck,buns,burn,carl,chad,citi,clay,club,coop,corp,cris,dale,dana,davi,dawn,dean,depp,dept,dian,dodg,dori,doug,doyl,duan,each,earl,engl,eric,etal,farm,fish,fred,gail,gari,gene,glen,golf,hall,hast,high,hill,hold,holm,home,hous,hugh,illa,iowa,iren,irma,jack,jame,jami,jana,jane,jani,jayn,jean,jeff,jill,joan,jodi,joel,john,jone,joyc,judi,juli,june,kane,kent,keun,kirk,kurt,kyle,ladd,lanc,land,lenz,leon,lewi,life,lisa,live,lllp,long,lori,loui,lust,lynn,main,mapl,mari,mark,merl,mgmt,moor,oliv,owen,park,paul,reed,rene,rick,rmdr,roak,rock,rose,ross,rusk,ruth,ryan,sage,sara,teri,tina,todd,toni,troy,trst,unit,wade,wayn,webb,wood,york,12790,aaron,allan,allen,alvin,amber,apart,arvin,ashbi,baker,barri,beard,betti,bever,beyer,billi,board,bonni,booth,brand,brent,brian,brown,bruce,bryan,bryce,carol,carri,casey,cathi,cecil,charl,chris,cindi,clair,clark,co-op,conni,craig, creek,cross,cuppl,curti,dairi,danni,daryl,david,debra,delor,denis,denni,devri,diana,divis,donna,edith,edwin,ehler,elain,ellen,elois,emili,eugen,field,first,floss,floyd,franc,frank,freda,front,georg,glenn,grain,harri,hazel,heali,helen,henri,holli,jacob,janet,janic,jason,jeann,jerri,jewel,jimmi,joann,julia,junip,karen,kathi,keith,kelli,kevin,l-est,laird,larri,laura,leagu,leona,leroy,lesli,linda,lloyd,louis,lowel,lynda,maher,maria,merci,meyer,mildr,mingo,monro,morri,nanci,natur,nicol,nolin,norma,nuzum,patti,paula,peggi,penni,peter,pierc,plumb,ralph,randi,revoc,robin,roger,roush,rusti,salli,sarah,savag,scott,sharp,shawn,smith,snook,spark,staci,state,steve,stock,store,sulli,susan,swank,swift,tammi,terri,thoma,tower,traci,travi,trust,union,vanze,vicki,wanda,water,wehrl,wendi,weyer,white,wilma,woodi,young,amanda,andrea,andrew,angela,arthur,ashley,assess,associ,atwood,backus,barton,baxter,brenda,butler,calvin,carrol,carson,cheryl,church,coleen,colett,colfax,colleg,conrad,cooper,corpor,counti ,daniel,darlen,darrel,darwin,deanna,decook,degoey,dejong,delmar,deraad,donald,donita,dougla,dwight,econom,edward,ernest,esther,evelyn,famili,farmer,farrel,fisher,forest,foster,franci,gannon,gerald,gloria,gordon,graham,guthri,hammer,hansen,hanson,harlan,harold,hesson,hewitt,howard,hunter,huyser,invest,jansen,jasper,jennif,jeremi,joseph,joshua,judith,justin,keenan,kelley,kendal,kimber,kramer,kristi,kunkel,legaci,legion,lester,locust,louden,machin,manatt,marcia,marion,market,marlen,marlin,martha,martin,marvin,materi,maxwel,maytag,meadow,melvin,michel,miller,morgan,nathan,nation,nelson,newton,nikkel,norman,osborn,oswalt,pamela,parker,paulin,philip,phylli,rachel,randal,rankin,reform,regina,renaud,rental,rhonda,rippey,robert,rodger,rodney,ronald,roorda,runner,russel,sandra,school,season,second,servic,sharon,sherri,sheryl,simbro,spring,steven,storag,sylvia,talsma,taylor,teresa,thelma,trstee,truste,vacant,valeri,valley,vansic,vanwyk,vaughn,verlan,vernon,walker,wallac,walnut,walter,warner,war ren,weaver,wesley,wilbur,wilson,wright,altemei,anthoni,barbara,beatric,benskin,beukema,bradley,brandon,brother,brummel,bucklin,campbel,carolyn,carriag,central,charlen,clayton,clement,cleveng,colleen,commerc,compani,conserv,convers,crystal,cynthia,deborah,degreef,develop,dezwart,diamond,dittmer,donovan,dorothi,elliott,exchang,faidley,foundat,gerdena,gifford,gilbert,gregori,hackert,heather,herbold,heritag,interst,jackson,jeffrey,jessica,johnson,kaisand,kathryn,kellogg,kenneth,kristin,laplant,lawrenc,leonard,lillian,lincoln,lynnett,maasdam,madison,maggard,marilyn,marjori,marshal,matthew,meisner,melinda,melissa,michael,michell,mitchel,moffitt,nichola,patrick,phillip,practic,raymond,rebecca,resourc,richard,rozenda,schmidt,schnell,schwarz,shannon,sherman,shirley,societi,stanley,stephen,stewart,straver,terlouw,theresa,timothi,vangorp,vanzant,vermeer,wildlif,william,wormley,zylstra,american,anderson,bartelma,benjamin,brandhof,broadway,buchanan,catherin,cemeteri,chadwick,charlott,christin,cli fford,communic,constanc,disclaim,district,farmland,ferguson,franklin,goodrich,hagedorn,harrison,humphrey,jonathan,kathleen,landgreb,langmaid,lynnvill,margaret,marietta,mcgregor,mckibbin,mcklveen,mcmurray,milligan,nearmyer,parsonag,patricia,peterson,pleasant,properti,railroad,robinson,rumbaugh,schuster,stephani,terpstra,thompson,vandalen,vansland,verploeg,virginia,winchest,woodland,bruxvoort,chichaqua,christian,christina,christoph,cleverley,communiti,davenport,elizabeth,enterpris,heartland,hutcheson,jacquelyn,jefferson,kimberley,methodist,northcutt,patterson,steenhoek,vanginkel,vanhemert,vankooten,vanmaanen,vanroekel,vanryswyk,vansoelen,willemsen,birkenholz,dunsbergen,kaldenberg,livingston,manufactur,montgomeri,profession,richardson,ro-den-cri,vanderhart,vanderkamp,vanderwilt,washington,birkenholtz,breckenridg,partnership,prendergast,trst/trstee,vanderleest,vangenderen,trust-truste,vanderweerdt,vanwyngarden,trust/trustee,trusts-truste,vandusseldorp,trust/trustees} | {0.00596667,0.00653333,0.006,0.00926667,0.0063,0.00603333,0.00396667,0.00756667,0.0046,0.0342333,0.0067,0.0133333,0.0252,0.109733,0.0114667,0.0091,0.0061,0.00353333,0.0371333,0.0176,0.0532333,0.0275667,0.0681667,0.00223333,0.00636667,0.0213333,0.0981,0.0081,0.00216667,0.00313333,0.0881667,0.0004,0.0038,0.00343333,0.00576667,0.00413333,0.0047,0.00403333,0.0025,0.0018,0.00196667,0.0031,0.00223333,0.0011,0.000666667,0.0013,0.00103333,0.0008,0.000433333,0.0007,0.0024,0.000666667,0.0005,0.00153333,0.0008,0.000966667,0.000366667,0.0006,0.0015,0.0004,0.00163333,0.0006,0.000766667,0.0004,0.000433333,0.00103333,0.000533333,0.00143333,0.000466667,0.0004,0.000933333,0.000666667,0.0008,0.000566667,0.000633333,0.0693667,0.00223333,0.0015,0.00476667,0.000933333,0.00223333,0.00376667,0.0584333,0.0007,0.00376667,0.0015,0.000966667,0.0107667,0.000433333,0.00283333,0.00173333,0.000866667,0.0004,0.000466667,0.108533,0.000766667,0.0006,0.00113333,0.0004,0.001,0.00136667,0.0014,0.0004,0.00136667,0.00043 3333,0.000666667,0.0151667,0.00116667,0.00116667,0.00126667,0.000866667,0.0007,0.000933333,0.0017,0.000633333,0.000766667,0.0017,0.00123333,0.000833333,0.0009,0.000866667,0.00133333,0.0004,0.000466667,0.000933333,0.00123333,0.00126667,0.000966667,0.0005,0.00133333,0.0004,0.000533333,0.000533333,0.000866667,0.0004,0.000766667,0.000466667,0.000533333,0.000766667,0.000833333,0.0007,0.000933333,0.0008,0.0011,0.000766667,0.0008,0.00113333,0.000433333,0.0006,0.0008,0.000533333,0.0005,0.000566667,0.0004,0.0007,0.0004,0.000633333,0.000466667,0.000866667,0.001,0.000866667,0.0006,0.00123333,0.000866667,0.0009,0.0009,0.000966667,0.0008,0.000733333,0.000633333,0.0008,0.0007,0.000366667,0.000366667,0.000466667,0.0007,0.001,0.000666667,0.000933333,0.000766667,0.000733333,0.000933333,0.000866667,0.0005,0.0006,0.000833333,0.000433333,0.000366667,0.0004,0.000433333,0.0007,0.0014,0.000766667,0.000766667,0.000733333,0.000733333,0.000566667,0.000633333,0.0004,0.0005,0.000466667,0.000433333,0.0007,0.0006 66667,0.000966667,0.000966667,0.00123333,0.0006,0.0006,0.000633333,0.0006,0.000733333,0.000566667,0.0005,0.0005,0.0004,0.0004,0.000733333,0.0006,0.0008,0.000566667,0.000366667,0.000466667,0.000466667,0.0005,0.000533333,0.000433333,0.000533333,0.000366667,0.000566667,0.000433333,0.000366667,0.000433333,0.000466667,0.0005,0.0004,0.000633333,0.000433333,0.000533333,0.0019,0.000433333,0.00706667,0.000366667,0.000433333,0.00136667,0.000433333,0.000633333,0.0004,0.000766667,0.000633333,0.00453333,0.0008,0.001,0.000966667,0.0153,0.0007,0.0174333,0.00226667,0.00103333,0.000366667,0.000533333,0.000766667,0.000366667,0.0027,0.00463333,0.000433333,0.0163333,0.000733333,0.00113333,0.000866667,0.000666667,0.00356667,0.00123333,0.0005,0.00146667,0.000633333,0.0014,0.000466667,0.000966667,0.0015,0.000433333,0.0207667,0.000433333,0.0006,0.000733333,0.00146667,0.003,0.000833333,0.000433333,0.000733333,0.00226667,0.0004,0.000366667,0.000366667,0.000466667,0.000966667,0.0009,0.0014,0.00156667,0.0007333 33,0.0005,0.0005,0.00123333,0.000366667,0.000366667,0.00153333,0.0009,0.0004,0.000566667,0.000366667,0.000633333,0.0006,0.000466667,0.00146667,0.0012,0.00666667,0.000433333,0.0005,0.000833333,0.00166667,0.0004,0.00313333,0.000433333,0.0006,0.000966667,0.00343333,0.0006,0.00116667,0.00226667,0.0004,0.0007,0.000366667,0.000433333,0.00143333,0.000933333,0.00123333,0.0007,0.00163333,0.0117,0.0166667,0.00173333,0.000533333,0.000366667,0.00403333,0.00136667,0.00106667,0.000766667,0.000433333,0.0004,0.00143333,0.000766667,0.00186667,0.000633333,0.0009,0.0006,0.000366667,0.000433333,0.0083,0.00123333,0.000366667,0.0012,0.01,0.000366667,0.000433333,0.00276667,0.000833333,0.0008,0.0038,0.0006,0.000533333,0.00173333,0.0011,0.00133333,0.0091,0.00216667,0.00303333,0.0019,0.0016,0.0006,0.000433333,0.000766667,0.000633333,0.000533333,0.000366667,0.0005,0.0004,0.000466667,0.0025,0.0005,0.000433333,0.000633333,0.0006,0.00136667,0.00353333,0.00193333,0.000633333,0.00193333,0.000433333,0.00123333,0.003 13333,0.0039,0.000533333,0.00963333,0.00446667,0.0005,0.000766667,0.000733333,0.000466667,0.000366667,0.00356667,0.00376667,0.000366667,0.000433333,0.000933333,0.005,0.000466667,0.00216667,0.000633333,0.0006,0.000433333,0.0026,0.0014,0.000533333,0.000533333,0.0004,0.0005,0.0025,0.0009,0.0009,0.0023,0.00163333,0.0004,0.00286667,0.000766667,0.000766667,0.000666667,0.000366667,0.000533333,0.0004,0.0019,0.0004,0.000433333,0.000733333,0.0008,0.000566667,0.0006,0.000733333,0.0004,0.00336667,0.00163333,0.00136667,0.000633333,0.000866667,0.00106667,0.0004,0.0008,0.000666667,0.0024,0.00183333,0.00213333,0.0005,0.000566667,0.00276667,0.0004,0.000533333,0.000366667,0.000433333,0.00483333,0.000766667,0.00166667,0.000433333,0.000766667,0.000433333,0.00136667,0.0029,0.00286667,0.0006,0.000633333,0.0023,0.000466667,0.0009,0.000466667,0.0109,0.00193333,0.0004,0.00103333,0.00476667,0.00153333,0.000933333,0.001,0.00326667,0.000433333,0.000666667,0.00103333,0.00103333,0.00153333,0.0004,0.0006,0.0015666 7,0.000533333,0.00173333,0.0005,0.000366667,0.000566667,0.0016,0.000466667,0.000733333,0.00116667,0.0006,0.0004,0.000733333,0.0004,0.000566667,0.0017,0.001,0.0004,0.000566667,0.00376667,0.00176667,0.00156667,0.000566667,0.0029,0.000366667,0.000466667,0.000833333,0.000666667,0.000566667,0.00343333,0.00113333,0.0019,0.00163333,0.00243333,0.00336667,0.0004,0.0068,0.00173333,0.000566667,0.0005,0.000666667,0.000633333,0.0075,0.000566667,0.0005,0.000433333,0.000366667,0.0006,0.000366667,0.0004,0.0009,0.000833333,0.0005,0.00223333,0.0008,0.00243333,0.00176667,0.000433333,0.0008,0.00106667,0.000366667,0.000633333,0.000533333,0.000366667,0.000366667,0.000833333,0.00113333,0.000666667,0.001,0.0015,0.00313333,0.0007,0.00236667,0.0008,0.000533333,0.000466667,0.000666667,0.000533333,0.0036,0.0005,0.000633333,0.0034,0.0007,0.000366667,0.0005,0.0044,0.001,0.000366667,0.0004,0.00123333,0.00266667,0.000566667,0.000366667,0.000466667,0.00176667,0.00366667,0.0006,0.000733333,0.000533333,0.0268333,0.000 366667,0.0018,0.0014,0.000366667,0.00133333,0.0008,0.0005,0.0004,0.0007,0.0006,0.000533333,0.0006,0.000766667,0.0005,0.00146667,0.000866667,0.0011,0.0005,0.000533333,0.00183333,0.0004,0.000466667,0.0004,0.00126667,0.00196667,0.000533333,0.000366667,0.00233333,0.0004,0.0011,0.00343333,0.000366667,0.000466667,0.00206667,0.000466667,0.000366667,0.000833333,0.00173333,0.0032,0.00296667,0.0009,0.00123333,0.000366667,0.0006,0.000466667,0.000366667,0.000566667,0.000466667,0.000366667,0.00496667,0.0004,0.0029,0.00133333,0.0004,0.00246667,0.000766667,0.0004,0.000666667,0.00636667,0.000366667,0.00103333,0.0005,0.0004,0.0004,0.0006,0.00173333,0.000866667,0.000933333,0.000966667,0.0004,0.000566667,0.000433333,0.000633333,0.000833333,0.000366667,0.00153333,0.0005,0.000666667,0.000633333,0.000533333,0.0011,0.00113333,0.0025,0.00453333,0.0019,0.000866667,0.00166667,0.0006,0.00166667,0.000533333,0.000466667,0.000366667,0.0004,0.0015,0.000433333,0.0004,0.000466667,0.000433333,0.000466667,0.0005,0.000 566667,0.0005,0.0005,0.0004,0.00123333,0.0009,0.0004,0.0004,0.000433333,0.000866667,0.0013,0.00176667,0.000366667,0.000833333,0.0004,0.0004,0.00143333,0.000666667,0.0015,0.0005,0.000633333,0.000433333,0.0006,0.0052,0.0007,0.00146667,0.000433333,0.0004,0.00193333,0.0006,0.000366667,0.0007,0.000866667,0.000533333,0.0016,0.000433333,0.000433333,0.000433333,0.0004,0.000533333,0.000966667,0.000566667,0.00723333,0.0005,0.0009,0.0044,0.001,0.000733333,0.00156667,0.0027,0.000666667,0.000366667,0.000466667,0.0028,0.0016,0.000533333,0.0012,0.0004,0.000766667,0.00346667,0.0006,0.0004,0.0021,0.0011,0.00156667,0.000433333,0.00146667,0.00876667,0.00106667,0.000433333,0.000433333,0.000566667,0.00506667,0.000433333,0.0009,0.0012,0.000666667,0.000733333,0.0016,0.0007,0.000666667,0.0011,0.000366667,0.000566667,0.000433333,0.0025,0.0011,0.000666667,0.000733333,0.0035,0.000366667,0.000366667,0.000533333,0.00106667,0.0007,0.000433333,0.0011,0.000733333,0.000433333,0.00136667,0.000766667,0.0013,0.0006,0.0 00533333,0.000366667,0.0005,0.000633333,0.00106667,0.00276667,0.000833333,0.000766667,0.000466667,0.00133333,0.00143333,0.000566667,0.00156667,0.000566667,0.000466667,0.0004,0.0004,0.00206667,0.0013,0.0006,0.000666667,0.00103333,0.0004,0.0004,0.000533333,0.00156667,0.000433333,0.000666667,0.0005,0.000833333,0.0021,0.0008,0.0032,0.0005,0.00226667,0.000633333,0.0012,0.000666667,0.00376667,0.0007,0.0005,0.00126667,0.00116667,0.0006,0.00106667,0.000466667,0.000466667,0.0004,0.0004,0.00443333,0.00103333,0.000566667,0.0017,0.000466667,0.000366667,0.000833333,0.0081,0.00123333,0.0004,0.000833333,0.000533333,0.0014,0.0004,0.000366667,0.00126667,0.00123333,0.0009,0.0062,0.0012,0.000633333,0.0018,0.000566667,0.0004,0.0007,0.003,0.000366667,0.00126667,0.000833333,0.0005,0.0004,0.00126667,0.000933333,0.00243333,0.001,0.0008,0.000833333,0.0021,0.00713333,0.000733333,0.000533333,0.000566667,0.00203333,0.000466667,0.000366667,0.000466667,0.000766667,0.000366667,0.0005,0.00173333,0.000533333,0.00056 6667,0.00116667,0.0006,0.0004,0.0004,0.000366667,0.0004,0.000466667,0.000366667,0.000466667,0.00103333,0.0004,0.000433333,0.000366667,0.0004,0.00223333,0.000433333,0.000733333,0.0006,0.00273333,0.0004,0.000566667,0.0004,0.0005,0.000466667,0.000433333,0.000833333,0.000433333,0.00293333,0.000733333,0.0009,0.0052,0.00296667,0.0006,0.000466667,0.000466667,0.000666667,0.000633333,0.0008,0.000433333,0.0004,0.000933333,0.0016,0.000766667,0.0004,0.0005,0.0004,0.0007,0.000466667,0.00163333,0.000466667,0.000866667,0.000433333,0.0012,0.000833333,0.000566667,0.000366667,0.0004,0.000866667,0.000633333,0.0006,0.000566667,0.000533333,0.00166667,0.000433333,0.0005,0.00113333,0.0013,0.0008,0.000933333,0.000466667,0.000466667,0.000666667,0.000933333,0.0005,0.000366667,0.0004,0.000366667,0.000366667,0.000366667,0.0004,0.000433333,0.000466667,0.000533333,0.00183333,0.003,0.0004,0.00113333,0.000866667,0.000366667,0.0004,0.000833333,0.0012,0.0006,0.000366667,0.00873333,0.000866667,0.000533333,0.00653333,0 .000366667,0.109733} | (4 rows)
Wow thats bad. Here's another link: http://camavision.com/dn/stats.txt -Andy
Andy Colson <andy@squeakycode.net> 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 been removing the name
> for simplicity. The dump of the table is here:
Thanks very much for sharing the data. But now I am well and truly
confused, because I still can't reproduce your results. I get
regression=# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=76.00..80.02 rows=1 width=72) (actual time=8.119..8.119 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
-> Bitmap Index Scan on search_key (cost=0.00..76.00 rows=1 width=0) (actual time=8.113..8.113 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
Total runtime: 8.210 ms
This is on 9.3 branch tip, not 9.3.9 which I don't have installed;
but I see no bug fixes related to GIN estimation in the commit logs
since 9.3.9.
Are you using any nondefault planner settings? Anything else
unusual about your installation?
regards, tom lane
On 12/30/2015 1:55 PM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.net> 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 been removing the name
>> for simplicity. The dump of the table is here:
>
> Thanks very much for sharing the data. But now I am well and truly
> confused, because I still can't reproduce your results. I get
>
> regression=# explain analyze
> select *
> from search
> where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on search (cost=76.00..80.02 rows=1 width=72) (actual time=8.119..8.119 rows=1 loops=1)
> Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
> -> Bitmap Index Scan on search_key (cost=0.00..76.00 rows=1 width=0) (actual time=8.113..8.113 rows=1 loops=1)
> Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
> Total runtime: 8.210 ms
>
> This is on 9.3 branch tip, not 9.3.9 which I don't have installed;
> but I see no bug fixes related to GIN estimation in the commit logs
> since 9.3.9.
>
> Are you using any nondefault planner settings? Anything else
> unusual about your installation?
>
> regards, tom lane
>
There are others, but I'll bet its:
random_page_cost = 1
The Others:
max_connections = 20
shared_buffers = 400MB
work_mem = 5MB
maintenance_work_mem = 64MB
effective_cache_size = 1700MB
synchronous_commit = off
effective_io_concurrency = 3
track_io_timing = on
max_locks_per_transaction = 2300
I can't honestly say why I've set random_page_cost. Its been way too
long for me to remember. The box is running a 4 drive sata software
raid 10, on Slackware64.
-Andy
On 12/30/2015 2:03 PM, Andy Colson wrote:
> On 12/30/2015 1:55 PM, Tom Lane wrote:
>> Andy Colson <andy@squeakycode.net> 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 been removing the name
>>> for simplicity. The dump of the table is here:
>>
>> Thanks very much for sharing the data. But now I am well and truly
>> confused, because I still can't reproduce your results. I get
>>
>> regression=# explain analyze
>> select *
>> from search
>> where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
>> QUERY PLAN
>> --------------------------------------------------------------------------------------------------------------------
>>
>> Bitmap Heap Scan on search (cost=76.00..80.02 rows=1 width=72)
>> (actual time=8.119..8.119 rows=1 loops=1)
>> Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
>> N:*'::text))
>> -> Bitmap Index Scan on search_key (cost=0.00..76.00 rows=1
>> width=0) (actual time=8.113..8.113 rows=1 loops=1)
>> Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
>> N:*'::text))
>> Total runtime: 8.210 ms
>>
>> This is on 9.3 branch tip, not 9.3.9 which I don't have installed;
>> but I see no bug fixes related to GIN estimation in the commit logs
>> since 9.3.9.
>>
>> Are you using any nondefault planner settings? Anything else
>> unusual about your installation?
>>
>> regards, tom lane
>>
>
> There are others, but I'll bet its:
>
> random_page_cost = 1
>
Humm, nope. I removed the config option, restart PG, then analyzed the
search table:
# show random_page_cost ;
random_page_cost
------------------
4
# analyze search;
And it still wont use the index. I'll tool around a little more and see
if I can find something.
Thanks much for all your help on this.
-Andy
Andy Colson <andy@squeakycode.net> 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
pg_config print?
regards, tom lane
On 12/30/2015 2:18 PM, Tom Lane wrote: > Andy Colson <andy@squeakycode.net> 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 > pg_config print? > > regards, tom lane > BINDIR = /usr/local/pg93/bin DOCDIR = /usr/local/pg93/share/doc/postgresql HTMLDIR = /usr/local/pg93/share/doc/postgresql INCLUDEDIR = /usr/local/pg93/include PKGINCLUDEDIR = /usr/local/pg93/include/postgresql INCLUDEDIR-SERVER = /usr/local/pg93/include/postgresql/server LIBDIR = /usr/local/pg93/lib PKGLIBDIR = /usr/local/pg93/lib/postgresql LOCALEDIR = /usr/local/pg93/share/locale MANDIR = /usr/local/pg93/share/man SHAREDIR = /usr/local/pg93/share/postgresql SYSCONFDIR = /usr/local/pg93/etc/postgresql PGXS = /usr/local/pg93/lib/postgresql/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/usr/local/pg93' '--with-perl' '--enable-thread-safety' '--build=x86_64-slackware-linux' 'build_alias=x86_64-slackware-linux' 'CFLAGS=-O2 -fPIC -DLINUX_OOM_SCORE_ADJ=0' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -fPIC -DLINUX_OOM_SCORE_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard CFLAGS_SL = -fpic LDFLAGS = -L../../../src/common -Wl,--as-needed -Wl,-rpath,'/usr/local/pg93/lib',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgport -lpgcommon -lz -lreadline -ltermcap -lcrypt -ldl -lm VERSION = PostgreSQL 9.3.9
Andy Colson <andy@squeakycode.net> 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 build you've got. But I'm
starting to get the feeling that you may not get an answer short
of tracing through gincostestimate to see where it's going nuts.
regards, tom lane
On 12/30/2015 2:33 PM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.net> 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 build you've got. But I'm
> starting to get the feeling that you may not get an answer short
> of tracing through gincostestimate to see where it's going nuts.
>
> regards, tom lane
>
The entire database is 78Gig, would you expect a "vacuum analyze" to fix
it? I never run it.
Cuz I started one, and its still going, but at this point right now it's
preferring indexed scans. So it seems fixed.
I'd ran: analyze jasperia.search
many times, before and after I'd emailed the list. I've rebuilt the
search table several times over, but never vacuumed it.
explain analyze
select *
from jasperia.search
where search_vec @@ to_tsquery_partial('213 E 13 ST N')
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=76.01..80.03 rows=1 width=73)
(actual time=62.803..62.804 rows=1 loops=1)
Recheck Cond: (search_vec @@
to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
':*'::text)))
-> Bitmap Index Scan on search_key (cost=0.00..76.01 rows=1
width=0) (actual time=62.797..62.797 rows=1 loops=1)
Index Cond: (search_vec @@
to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
':*'::text)))
Total runtime: 62.869 ms
(* The vacuum analyze is still running *)
On 12/30/2015 2:39 PM, Andy Colson wrote: > On 12/30/2015 2:33 PM, Tom Lane wrote: >> Andy Colson <andy@squeakycode.net> 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 build you've got. But I'm >> starting to get the feeling that you may not get an answer short >> of tracing through gincostestimate to see where it's going nuts. >> >> regards, tom lane >> > > The entire database is 78Gig, would you expect a "vacuum analyze" to fix > it? I never run it. > 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. -Andy
Andy Colson <andy@squeakycode.net> 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 didn't say what your table recreation process is, but now I bet
it involves create the table, create the index, *then* fill the table.
I was just running the dump script, which creates the index last.
If I do it the other way then I get insane estimates.
Tracing through that, when gincostestimate looks at the GIN index's
metapage stats, it sees this:
(gdb) p *metadata
$1 = {head = 2, tail = 136, tailFreeSize = 3272, nPendingPages = 135,
nPendingHeapTuples = 33424, nTotalPages = 2, nEntryPages = 1,
nDataPages = 0, nEntries = 0, ginVersion = 2}
ie, the page counts are as of the time of index creation not current.
The insanity must come from trying to scale these up to the current index
size and getting silly results. In particular, it's still gonna end up
with numDataPages equal to zero, which I bet is bad news ...
After VACUUM I see
(gdb) p *metadata
$2 = {head = 4294967295, tail = 4294967295, tailFreeSize = 0,
nPendingPages = 0, nPendingHeapTuples = 0, nTotalPages = 685,
nEntryPages = 410, nDataPages = 16, nEntries = 44125, ginVersion = 2}
and the cost estimate is far saner.
regards, tom lane
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 also set a default for than on a specific database, or a specific user, using ALTER DATABASE SET or ALTER USER SET.) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com