Thread: [GENERAL] Very strange problem on index
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=0loops=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
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
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
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=0loops=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...
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=0loops=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.