pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSearching in varchar column having 100M records
13+ messages / 8 participants
[nested] [flat]
* Searching in varchar column having 100M records
@ 2019-07-17 11:03 mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 11:53 ` Re: Searching in varchar column having 100M records Sergei Kornilov <sk@zsrv.org>
2019-07-17 13:57 ` Re: Searching in varchar column having 100M records David G. Johnston <david.g.johnston@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 3 replies; 13+ messages in thread
From: mayank rupareliya @ 2019-07-17 11:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
My table is having data like below with 100M records (contains all dummy
data). I am having btree index on column ("field").
*While searching for any text from that column takes longer (more than 1
minute).*
user Id field
d848f466-5e12-46e7-acf4-e12aff592241 Northern Arkansas College
24c32757-e6a8-4dbd-aac7-1efd867156ce female
6e225c57-c1d1-48a5-b9aa-513223efc81b 1.0, 3.67, 3.67, 4.67, 7.0, 3.0
088c6342-a240-45a7-9d12-e0e707292031 Weber
b05088cf-cba6-4bd7-8f8f-1469226874d0 addd#$e@aaa.com
Table and index are created using following query.
create table fields(user_id varchar(64), field varchar(64));
CREATE INDEX index_field ON public.fields USING btree (field);
Search Query:
EXPLAIN (ANALYZE, BUFFERS) select * from fields where field='Mueller';
Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=55)
(actual time=88.017..65358.548 rows=31882 loops=1)
Recheck Cond: ((field)::text = 'Mueller'::text)
Heap Blocks: exact=31403
Buffers: shared hit=2 read=31492
-> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586
width=0) (actual time=55.960..55.960 rows=31882 loops=1)
Index Cond: ((field)::text = 'Mueller'::text)
Buffers: shared read=91
Planning Time: 0.331 ms
Execution Time: 65399.314 ms
Any suggestions for improvement?
Best Regards,
Mayank
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-17 11:53 ` Sergei Kornilov <sk@zsrv.org>
2019-07-17 12:48 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
2 siblings, 1 reply; 13+ messages in thread
From: Sergei Kornilov @ 2019-07-17 11:53 UTC (permalink / raw)
To: mayank rupareliya <mayankjr03@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hello
Please recheck with track_io_timing = on in configuration. explain (analyze,buffers) with this option will report how many time we spend during i/o
> Buffers: shared hit=2 read=31492
31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD
Your query reads table data from disks (well, or from OS cache). You need more RAM for shared_buffers or disks with better performance.
regards, Sergei
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 11:53 ` Re: Searching in varchar column having 100M records Sergei Kornilov <sk@zsrv.org>
@ 2019-07-17 12:48 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-17 13:00 ` Re: Searching in varchar column having 100M records Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2019-07-17 12:48 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; +Cc: mayank rupareliya <mayankjr03@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Wed, Jul 17, 2019 at 02:53:20PM +0300, Sergei Kornilov wrote:
>Hello
>
>Please recheck with track_io_timing = on in configuration. explain
>(analyze,buffers) with this option will report how many time we spend
>during i/o
>
>> Buffers: shared hit=2 read=31492
>
>31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD
>
>Your query reads table data from disks (well, or from OS cache). You need
>more RAM for shared_buffers or disks with better performance.
>
Either that, or try creating a covering index, so that the query can do an
index-only scan. That might reduce the amount of IO against the table, and
in the index the data should be located close to each other (same page or
pages close to each other).
So try something like
CREATE INDEX ios_idx ON table (field, user_id);
and make sure the table is vacuumed often enough (so that the visibility
map is up to date).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 11:53 ` Re: Searching in varchar column having 100M records Sergei Kornilov <sk@zsrv.org>
2019-07-17 12:48 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-07-17 13:00 ` Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 0 replies; 13+ messages in thread
From: Andreas Kretschmer @ 2019-07-17 13:00 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Am 17.07.19 um 14:48 schrieb Tomas Vondra:
> Either that, or try creating a covering index, so that the query can
> do an
> index-only scan. That might reduce the amount of IO against the table,
> and
> in the index the data should be located close to each other (same page or
> pages close to each other).
>
> So try something like
>
> CREATE INDEX ios_idx ON table (field, user_id);
>
> and make sure the table is vacuumed often enough (so that the visibility
> map is up to date).
yeah, and please don't use varchar(64), but instead UUID for the user_id
- field to save space on disk and for faster comparison.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-17 13:57 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: David G. Johnston @ 2019-07-17 13:57 UTC (permalink / raw)
To: mayank rupareliya <mayankjr03@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Jul 17, 2019 at 4:04 AM mayank rupareliya <mayankjr03@gmail.com>
wrote:
> create table fields(user_id varchar(64), field varchar(64));
> CREATE INDEX index_field ON public.fields USING btree (field);
>
> Any suggestions for improvement?
>
Reduce the number of rows by constructing a relationally normalized data
model.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-17 22:54 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Gavin Flower @ 2019-07-17 22:54 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 17/07/2019 23:03, mayank rupareliya wrote:
[...]
> Table and index are created using following query.
>
> create table fields(user_id varchar(64), field varchar(64));
> CREATE INDEX index_field ON public.fields USING btree (field);
[...]
Any particular reason for using varchar instead of text, for field?
Also, as Andreas pointed out, use UUID for the user_id.
Cheers,
Gavin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
@ 2019-07-18 11:51 ` mayank rupareliya <mayankjr03@gmail.com>
2019-07-18 12:41 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-18 22:03 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 2 replies; 13+ messages in thread
From: mayank rupareliya @ 2019-07-18 11:51 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
*Please recheck with track_io_timing = on in configuration. explain
(analyze,buffers) with this option will report how many time we spend
during i/o*
*> Buffers: shared hit=2 read=31492*
*31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD*
*Your query reads table data from disks (well, or from OS cache). You need
more RAM for shared_buffers or disks with better performance.*
Thanks Sergei..
*track_io_timing = on helps.. Following is the result after changing that
config.*
Aggregate (cost=10075.78..10075.79 rows=1 width=8) (actual
time=63088.198..63088.199 rows=1 loops=1)
Buffers: shared read=31089
I/O Timings: read=61334.252
-> Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=0)
(actual time=69.509..63021.448 rows=31414 loops=1)
Recheck Cond: ((field)::text = 'Klein'::text)
Heap Blocks: exact=30999
Buffers: shared read=31089
I/O Timings: read=61334.252
-> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586
width=0) (actual time=58.671..58.671 rows=31414 loops=1)
Index Cond: ((field)::text = 'Klein'::text)
Buffers: shared read=90
I/O Timings: read=45.316
Planning Time: 66.435 ms
Execution Time: 63088.774 ms
*So try something like*
* CREATE INDEX ios_idx ON table (field, user_id);*
*and make sure the table is vacuumed often enough (so that the visibility*
*map is up to date).*
Thanks Tomas... I tried this and result improved but not much.
Thanks Andreas, David, Gavin
*Any particular reason for using varchar instead of text, for field?* No
use UUID for the user_id. Agreed
*Regards,Mayank*
On Thu, Jul 18, 2019 at 4:25 AM Gavin Flower <GavinFlower@archidevsys.co.nz>
wrote:
> On 17/07/2019 23:03, mayank rupareliya wrote:
> [...]
> > Table and index are created using following query.
> >
> > create table fields(user_id varchar(64), field varchar(64));
> > CREATE INDEX index_field ON public.fields USING btree (field);
>
> [...]
>
> Any particular reason for using varchar instead of text, for field?
>
> Also, as Andreas pointed out, use UUID for the user_id.
>
>
> Cheers,
> Gavin
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-18 12:41 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 14:13 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2019-07-18 12:41 UTC (permalink / raw)
To: mayank rupareliya <mayankjr03@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Jul 18, 2019 at 05:21:49PM +0530, mayank rupareliya wrote:
>*Please recheck with track_io_timing = on in configuration. explain
>(analyze,buffers) with this option will report how many time we spend
>during i/o*
>
>*> Buffers: shared hit=2 read=31492*
>
>*31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD*
>
>*Your query reads table data from disks (well, or from OS cache). You need
>more RAM for shared_buffers or disks with better performance.*
>
>
>Thanks Sergei..
>*track_io_timing = on helps.. Following is the result after changing that
>config.*
>
>Aggregate (cost=10075.78..10075.79 rows=1 width=8) (actual
>time=63088.198..63088.199 rows=1 loops=1)
> Buffers: shared read=31089
> I/O Timings: read=61334.252
> -> Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=0)
>(actual time=69.509..63021.448 rows=31414 loops=1)
> Recheck Cond: ((field)::text = 'Klein'::text)
> Heap Blocks: exact=30999
> Buffers: shared read=31089
> I/O Timings: read=61334.252
> -> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586
>width=0) (actual time=58.671..58.671 rows=31414 loops=1)
> Index Cond: ((field)::text = 'Klein'::text)
> Buffers: shared read=90
> I/O Timings: read=45.316
>Planning Time: 66.435 ms
>Execution Time: 63088.774 ms
>
How did that help? It only gives you insight that it's really the I/O that
takes time. You need to reduce that, somehow.
>
>*So try something like*
>
>* CREATE INDEX ios_idx ON table (field, user_id);*
>
>*and make sure the table is vacuumed often enough (so that the visibility*
>*map is up to date).*
>
>Thanks Tomas... I tried this and result improved but not much.
>
Well, you haven't shown us the execution plan, so it's hard to check why
it did not help much and give you further advice.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-18 12:41 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-07-19 14:13 ` mayank rupareliya <mayankjr03@gmail.com>
2019-07-19 17:33 ` Re: Searching in varchar column having 100M records Michael Lewis <mlewis@entrata.com>
2019-07-19 18:39 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 13+ messages in thread
From: mayank rupareliya @ 2019-07-19 14:13 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
Well, you haven't shown us the execution plan, so it's hard to check why
it did not help much and give you further advice.
This is the latest query execution with explain after adding indexing on
both columns.
Aggregate (cost=174173.57..174173.58 rows=1 width=8) (actual
time=65087.657..65087.658 rows=1 loops=1)
-> Bitmap Heap Scan on fields (cost=1382.56..174042.61 rows=52386
width=0) (actual time=160.340..65024.533 rows=31857 loops=1)
Recheck Cond: ((field)::text = 'Champlin'::text)
Heap Blocks: exact=31433
-> Bitmap Index Scan on index_field (cost=0.00..1369.46
rows=52386 width=0) (actual time=125.078..125.079 rows=31857 loops=1)
Index Cond: ((field)::text = 'Champlin'::text)
Planning Time: 8.595 ms
Execution Time: 65093.508 ms
On Thu, Jul 18, 2019 at 6:11 PM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Thu, Jul 18, 2019 at 05:21:49PM +0530, mayank rupareliya wrote:
> >*Please recheck with track_io_timing = on in configuration. explain
> >(analyze,buffers) with this option will report how many time we spend
> >during i/o*
> >
> >*> Buffers: shared hit=2 read=31492*
> >
> >*31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD*
> >
> >*Your query reads table data from disks (well, or from OS cache). You need
> >more RAM for shared_buffers or disks with better performance.*
> >
> >
> >Thanks Sergei..
> >*track_io_timing = on helps.. Following is the result after changing that
> >config.*
> >
> >Aggregate (cost=10075.78..10075.79 rows=1 width=8) (actual
> >time=63088.198..63088.199 rows=1 loops=1)
> > Buffers: shared read=31089
> > I/O Timings: read=61334.252
> > -> Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=0)
> >(actual time=69.509..63021.448 rows=31414 loops=1)
> > Recheck Cond: ((field)::text = 'Klein'::text)
> > Heap Blocks: exact=30999
> > Buffers: shared read=31089
> > I/O Timings: read=61334.252
> > -> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586
> >width=0) (actual time=58.671..58.671 rows=31414 loops=1)
> > Index Cond: ((field)::text = 'Klein'::text)
> > Buffers: shared read=90
> > I/O Timings: read=45.316
> >Planning Time: 66.435 ms
> >Execution Time: 63088.774 ms
> >
>
> How did that help? It only gives you insight that it's really the I/O that
> takes time. You need to reduce that, somehow.
>
> >
> >*So try something like*
> >
> >* CREATE INDEX ios_idx ON table (field, user_id);*
> >
> >*and make sure the table is vacuumed often enough (so that the visibility*
> >*map is up to date).*
> >
> >Thanks Tomas... I tried this and result improved but not much.
> >
>
> Well, you haven't shown us the execution plan, so it's hard to check why
> it did not help much and give you further advice.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-18 12:41 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 14:13 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-19 17:33 ` Michael Lewis <mlewis@entrata.com>
1 sibling, 0 replies; 13+ messages in thread
From: Michael Lewis @ 2019-07-19 17:33 UTC (permalink / raw)
To: mayank rupareliya <mayankjr03@gmail.com>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
On Fri, Jul 19, 2019 at 8:13 AM mayank rupareliya <mayankjr03@gmail.com>
wrote:
> Well, you haven't shown us the execution plan, so it's hard to check why
> it did not help much and give you further advice.
>
>
> This is the latest query execution with explain after adding indexing on
> both columns.
>
> Aggregate (cost=174173.57..174173.58 rows=1 width=8) (actual
> time=65087.657..65087.658 rows=1 loops=1)
> -> Bitmap Heap Scan on fields (cost=1382.56..174042.61 rows=52386
> width=0) (actual time=160.340..65024.533 rows=31857 loops=1)
> Recheck Cond: ((field)::text = 'Champlin'::text)
> Heap Blocks: exact=31433
> -> Bitmap Index Scan on index_field (cost=0.00..1369.46
> rows=52386 width=0) (actual time=125.078..125.079 rows=31857 loops=1)
> Index Cond: ((field)::text = 'Champlin'::text)
> Planning Time: 8.595 ms
> Execution Time: 65093.508 ms
>
>>
>>
Are you on a solid state drive? If so, have you tried setting
effective_io_concurrency to 200 or 300 and checking performance? Given
nearly all of the execution time is doing a bitmap heap scan, I wonder
about adjusting this.
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-B...
effective_io_concurrency
"The allowed range is 1 to 1000, or zero to disable issuance of
asynchronous I/O requests. Currently, this setting only affects bitmap heap
scans."
"The default is 1 on supported systems, otherwise 0. "
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-18 12:41 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 14:13 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-19 18:39 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 20:51 ` Re: Searching in varchar column having 100M records mgbii bax <gezeala@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2019-07-19 18:39 UTC (permalink / raw)
To: mayank rupareliya <mayankjr03@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, Jul 19, 2019 at 07:43:26PM +0530, mayank rupareliya wrote:
>Well, you haven't shown us the execution plan, so it's hard to check why
>it did not help much and give you further advice.
>
>
>This is the latest query execution with explain after adding indexing on
>both columns.
>
>Aggregate (cost=174173.57..174173.58 rows=1 width=8) (actual
>time=65087.657..65087.658 rows=1 loops=1)
> -> Bitmap Heap Scan on fields (cost=1382.56..174042.61 rows=52386
>width=0) (actual time=160.340..65024.533 rows=31857 loops=1)
> Recheck Cond: ((field)::text = 'Champlin'::text)
> Heap Blocks: exact=31433
> -> Bitmap Index Scan on index_field (cost=0.00..1369.46
>rows=52386 width=0) (actual time=125.078..125.079 rows=31857 loops=1)
> Index Cond: ((field)::text = 'Champlin'::text)
>Planning Time: 8.595 ms
>Execution Time: 65093.508 ms
>
That very clearly does not use the index-only scan, so it's not
surprising it's not any faster. You need to find out why the planner
makes that decision.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-18 12:41 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 14:13 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-19 18:39 ` Re: Searching in varchar column having 100M records Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-07-19 20:51 ` mgbii bax <gezeala@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: mgbii bax @ 2019-07-19 20:51 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: mayank rupareliya <mayankjr03@gmail.com>; pgsql-performance@lists.postgresql.org
Another suggestion, try to cluster the table using the index for the
"field" column, then analyze. If you're on a spinning disk it will help if
you sort your search "field" during bulk insert.
--
regards
marie g. bacuno ii
On Fri, Jul 19, 2019 at 11:39 AM Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:
> On Fri, Jul 19, 2019 at 07:43:26PM +0530, mayank rupareliya wrote:
> >Well, you haven't shown us the execution plan, so it's hard to check why
> >it did not help much and give you further advice.
> >
> >
> >This is the latest query execution with explain after adding indexing on
> >both columns.
> >
> >Aggregate (cost=174173.57..174173.58 rows=1 width=8) (actual
> >time=65087.657..65087.658 rows=1 loops=1)
> > -> Bitmap Heap Scan on fields (cost=1382.56..174042.61 rows=52386
> >width=0) (actual time=160.340..65024.533 rows=31857 loops=1)
> > Recheck Cond: ((field)::text = 'Champlin'::text)
> > Heap Blocks: exact=31433
> > -> Bitmap Index Scan on index_field (cost=0.00..1369.46
> >rows=52386 width=0) (actual time=125.078..125.079 rows=31857 loops=1)
> > Index Cond: ((field)::text = 'Champlin'::text)
> >Planning Time: 8.595 ms
> >Execution Time: 65093.508 ms
> >
>
> That very clearly does not use the index-only scan, so it's not
> surprising it's not any faster. You need to find out why the planner
> makes that decision.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Searching in varchar column having 100M records
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 22:54 ` Re: Searching in varchar column having 100M records Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` Re: Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
@ 2019-07-18 22:03 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
1 sibling, 0 replies; 13+ messages in thread
From: Gavin Flower @ 2019-07-18 22:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 18/07/2019 23:51, mayank rupareliya wrote:
[...]
> Thanks Andreas, David, Gavin
>
> /Any particular reason for using varchar instead of text, for field?/ No
>
> use UUID for the user_id.Agreed
/[...]/
/Use of text is preferred, but I can't see it making any significant
difference to performance -- but I could be wrong!/
/Cheers,
Gavin
/
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2019-07-19 20:51 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-17 11:03 Searching in varchar column having 100M records mayank rupareliya <mayankjr03@gmail.com>
2019-07-17 11:53 ` Sergei Kornilov <sk@zsrv.org>
2019-07-17 12:48 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-17 13:00 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2019-07-17 13:57 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-17 22:54 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-07-18 11:51 ` mayank rupareliya <mayankjr03@gmail.com>
2019-07-18 12:41 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 14:13 ` mayank rupareliya <mayankjr03@gmail.com>
2019-07-19 17:33 ` Michael Lewis <mlewis@entrata.com>
2019-07-19 18:39 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-07-19 20:51 ` mgbii bax <gezeala@gmail.com>
2019-07-18 22:03 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox