From: | Job <Job(at)colliniconsulting(dot)it> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Very strange problem on index |
Date: | 2017-02-02 12:18:05 |
Message-ID: | 88EF58F000EC4B4684700C2AA3A73D7A08054EACC4FE@W2008DC01.ColliniConsulting.lan |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi guys,
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...
Thank you!
/F
From | Date | Subject | |
---|---|---|---|
Next Message | Job | 2017-02-02 12:53:58 | (solved) R: Very strange problem on index |
Previous Message | alexanderfelipewo | 2017-02-02 11:27:45 | pgbouncer increase pool_size, reload does not work |