Very strange problem on index

I create a table copy, with indexes, and it worked perfecly.
Then i dropped the original table and recreated it back by a copy of the newest.

Same problems, lack during searches and indexes not used.

I restart Postgresql 9.6.1 engine and now index is working perfectly!

Maybe a cache or something other problems?

I am really curious to understand why!

Thank you!
/F

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/02/17 12:53, Job wrote:

I create a table copy, with indexes, and it worked perfecly.
Then i dropped the original table and recreated it back by a copy of the newest.

Same problems, lack during searches and indexes not used.

I didn't see your original message so may have missed this, but did you
run ANALYZE on the table after creating it? - Depending on how you
created the table, the statistics needed by the planner may or may not
have been up to date.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Raymond,

Thank nyou for your appreciated feedback.

Here is the original message:

i really strange problem, quite near to paranormal, is occurring during a server migration.

We have a table with some millions of record, perfectly working on other Postgresql 9.6.1 machines:

Table "public.webrecord"
Column | Type | Modifiers
-----------+----------------------+-------------------------------------
-----------+----------------------+-------------------
id | integer | not null default nextval('webrecord_id_seq'::regclass)
categoria | character varying |
dominio | character varying |
country | character varying(5) |
Indexes:
"keywebrecord" PRIMARY KEY, btree (id)
"dominio_idx" btree (dominio)

By entering a simple query:

Select * from webrecord where dominio='sito.com' it wait some seconds and cpu raise up.

I tried to:
- delete the tabel
- recreate the schema again
- re-populating it
- reindexing
- vacuum

But nothing happened.
The query planne says it scans lots of rows:

explain analyze select * from webrecord where dominio='crl.microsoft.com';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..144045.21 rows=62073 width=92) (actual time=1096.202..1096.202 rows=0 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on webrecord (cost=0.00..136837.91 rows=15518 width=92) (actual time=1063.277..1063.277 rows=0 loops=5)
Filter: ((dominio)::text = 'crl.microsoft.com'::text)
Rows Removed by Filter: 2482938 Planning time: 0.119 ms Execution time: 1107.846 ms

On other machine the results are immediate!

I have finished my tries...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2 February 2017 at 14:57, Job <Job@colliniconsulting.it> wrote:

Hi Raymond,

Thank nyou for your appreciated feedback.

But what's your answer to his question? You still didn't tell.

Here is the original message:

i really strange problem, quite near to paranormal, is occurring during a server migration.

We have a table with some millions of record, perfectly working on other Postgresql 9.6.1 machines:

Table "public.webrecord"
Column | Type | Modifiers
-----------+----------------------+-------------------------------------
-----------+----------------------+-------------------
id | integer | not null default nextval('webrecord_id_seq'::regclass)
categoria | character varying |
dominio | character varying |
country | character varying(5) |
Indexes:
"keywebrecord" PRIMARY KEY, btree (id)
"dominio_idx" btree (dominio)

By entering a simple query:

Select * from webrecord where dominio='sito.com' it wait some seconds and cpu raise up.

I tried to:
- delete the tabel
- recreate the schema again
- re-populating it
- reindexing
- vacuum

But nothing happened.
The query planne says it scans lots of rows:

explain analyze select * from webrecord where dominio='crl.microsoft.com';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..144045.21 rows=62073 width=92) (actual time=1096.202..1096.202 rows=0 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on webrecord (cost=0.00..136837.91 rows=15518 width=92) (actual time=1063.277..1063.277 rows=0 loops=5)
Filter: ((dominio)::text = 'crl.microsoft.com'::text)
Rows Removed by Filter: 2482938 Planning time: 0.119 ms Execution time: 1107.846 ms

On other machine the results are immediate!

I have finished my tries...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general