From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Job <Job(at)colliniconsulting(dot)it> |
Cc: | "rod(at)iol(dot)ie" <rod(at)iol(dot)ie>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: (solved) R: Very strange problem on index |
Date: | 2017-02-02 16:46:36 |
Message-ID: | CAF-3MvOv-oVFZXN2z6W-9q=MxP1F1VyB8Gxe+yz+3VUH4hi89Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2 February 2017 at 14:57, Job <Job(at)colliniconsulting(dot)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(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | JP Jacoupy | 2017-02-02 17:15:43 | Synchronous Commit, WAL archiving and statement_timeout |
Previous Message | Moreno Andreo | 2017-02-02 16:24:39 | Re: [SPAM] AD(Active Directory) groups concepts in postgres |