agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedOptimizing query
41+ messages / 22 participants
[nested] [flat]
* Optimizing query
@ 2001-09-24 15:57 Pedro Alves <pmalves@think.co.pt>
0 siblings, 0 replies; 41+ messages in thread
From: Pedro Alves @ 2001-09-24 15:57 UTC (permalink / raw)
To: pgsql-general
Hi! I'm having trouble in a simple query who is not being optimized
the way it should (apparently). The query is as follows:
SELECT DISTINCT e.e_tipo from requisicaoanalise ra, exame e where
ra.ra_reqnum='20010901798' and e.e_req=ra.ra_id;
table requisicaoanalise has +- 10000 rows, and exame hash +- 100000;
This query takes a lot of time (+- 5 secs on my system: psql
(PostgreSQL) 7.1RC3). I have indexes in ra_reqnum as in e_req and ra_id.
What I think is happening here is that postgres 1st evaluates
"e.e_req=ra.ra_id" ans only then "ra.ra_reqnum='20010901798", which is
obviously more time-consuming. If I rewrite the query as
SELECT DISTINCT e_tipo from exame e where e_req in ( select ra_id from
requisicaoanalise where ra_reqnum='20010901798');
the result comes instantly.
Is there any way to specify the precedence of the conditions in the
prior query? I don't have much experienece with the EXPLAIN, but here it
goes:
explain SELECT DISTINCT e.e_tipo from requisicaoanalise ra, exame e
where ra.ra_reqnum='20010901798' and e.e_req=ra.ra_id;
NOTICE: QUERY PLAN:
Unique (cost=22662.94..22867.50 rows=8182 width=12)
-> Sort (cost=22662.94..22662.94 rows=81821 width=12)
-> Merge Join (cost=13756.59..15219.60 rows=81821 width=12)
-> Sort (cost=64.94..64.94 rows=70 width=4)
-> Index Scan using requisicaoanalise_reqnum_idx on
requisicaoanalise ra (cost=0.00..62.79 rows=70 width=4)
-> Sort (cost=13691.65..13691.65 rows=116971 width=8)
-> Seq Scan on exame e (cost=0.00..2835.71 rows=116971
width=8
EXPLAIN SELECT DISTINCT e_tipo from exame e where e_req in ( select
ra_id from requisicaoanalise where ra_reqnum='20010901798');
NOTICE: QUERY PLAN:
Unique (cost=7358767.76..7359060.18 rows=11697 width=4)
-> Sort (cost=7358767.76..7358767.76 rows=116971 width=4)
-> Seq Scan on exame e (cost=0.00..7348057.26 rows=116971 width=4)
SubPlan
-> Materialize (cost=62.79..62.79 rows=70 width=4)
-> Index Scan using requisicaoanalise_reqnum_idx on
requisicaoanalise (cost=0.00..62.79 rows=70 width=4)
Thanks!
--
Pedro Miguel G. Alves
THINK - Tecnologias de Informação
Av. Defensores de Chaves nº 15 4ºD, 1000-109 Lisboa Portugal
Tel: +351 21 3590285 Fax: +351 21 3582729
HomePage: www.think.co.pt
^ permalink raw reply [nested|flat] 41+ messages in thread
* optimizing query
@ 2003-01-22 10:30 Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 41+ messages in thread
From: Chantal Ackermann @ 2003-01-22 10:30 UTC (permalink / raw)
To: pgsql-general
hello all,
I am getting the following output from EXPLAIN, concerning a query with
joins. The merge uses index scans but takes too long, in my opinion. The
query is in fact only a part (subquery) of another one, but it is the
bottle neck.
As I am quite ignorant in optimizing queries, and I have no idea where
to find documentation on the net on how to learn optimizing my queries,
I am posting this here in hope someone will give me either tips how to
optimize, or where to find some tutorial that could help me get along on
my own.
dropping the "DISTINCT" has some effect, but I can't really do without.
Thank you
Chantal
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
relate=# explain SELECT DISTINCT gene.gene_name,
gene_occurrences_puid.puid FROM disease, gene, disease_occurrences_puid,
gene_occurrences_puid WHERE
disease_occurrences_puid.puid=gene_occurrences_puid.puid AND
disease.disease_id=disease_occurrences_puid.disease_id AND
gene.gene_id=gene_occurrences_puid.gene_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=426503.59..436839.47 rows=137812 width=41)
-> Sort (cost=426503.59..429948.88 rows=1378118 width=41)
Sort Key: gene.gene_name, gene_occurrences_puid.puid
-> Hash Join (cost=67813.96..162375.07 rows=1378118 width=41)
Hash Cond: ("outer".disease_id = "inner".disease_id)
-> Merge Join (cost=63671.50..98237.36 rows=1378118
width=37)
Merge Cond: ("outer".puid = "inner".puid)
-> Index Scan using disease_occpd_puid_i on
disease_occurrences_puid (cost=0.00..14538.05 rows=471915 width=8)
-> Sort (cost=63671.50..64519.87 rows=339347
width=29)
Sort Key: gene_occurrences_puid.puid
-> Merge Join (cost=0.00..22828.18
rows=339347 width=29)
Merge Cond: ("outer".gene_id =
"inner".gene_id)
-> Index Scan using gene_pkey on gene
(cost=0.00..7668.59 rows=218085 width=21)
-> Index Scan using gene_id_puid_uni
on gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8)
-> Hash (cost=3167.97..3167.97 rows=164597 width=4)
-> Seq Scan on disease (cost=0.00..3167.97
rows=164597 width=4)
(16 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: optimizing query
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
@ 2003-01-22 16:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Re: optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
0 siblings, 1 reply; 41+ messages in thread
From: Stephan Szabo @ 2003-01-22 16:20 UTC (permalink / raw)
To: Chantal Ackermann <chantal.ackermann@biomax.de>; +Cc: pgsql-general; pgsql-performance@postgresql.org
(Replying to general and performance in a hope to move this
to performance after a couple of replies).
On Wed, 22 Jan 2003, Chantal Ackermann wrote:
> I am getting the following output from EXPLAIN, concerning a query with
> joins. The merge uses index scans but takes too long, in my opinion. The
> query is in fact only a part (subquery) of another one, but it is the
> bottle neck.
>
> As I am quite ignorant in optimizing queries, and I have no idea where
> to find documentation on the net on how to learn optimizing my queries,
> I am posting this here in hope someone will give me either tips how to
> optimize, or where to find some tutorial that could help me get along on
> my own.
>
> dropping the "DISTINCT" has some effect, but I can't really do without.
The first thing is, have you done ANALYZE recently to make sure that the
statistics are correct and what does EXPLAIN ANALYZE give you (that will
run the query and give the estimate and actual). Also, if you haven't
vacuumed recently, you may want to vacuum full.
How many rows are there on gene, disease and both occurrances tables?
I'd wonder if perhaps using explicit sql join syntax (which postgres uses
to constrain order) to join disease and disease_occurrences_puid before
joining it to the other two would be better or worse in practice.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: optimizing query
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-01-23 09:16 ` Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-23 15:05 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 15:26 ` Re: [PERFORM] optimizing query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 41+ messages in thread
From: Chantal Ackermann @ 2003-01-23 09:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-general; pgsql-performance@postgresql.org
hi Stephan,
thank you for your reply.
I ran vacuum analyze before calling explain. As this is a newly built
database where no rows have been deleted, yet, I thought vacuum full
would have no effect. In fact, BEFORE running vacuum full, the cost of
the query is estimates by explain analyze as 33 secs, and AFTER running
it, the cost is estimate to be 43 secs??? (Hey, I want at least the 10
secs back ;-) )
I have just installed this database on a "bigger" (see the system info
further down) machine, and I expected the queries would run _really_
fast. especially, as there is a lot more data to be inserted in the
occurrences tables.
This is the row count of the tables and the output of explain analyze
before and after running vacuum full (after that, I listed some system
and postgresql information):
relate=# select count(*) from gene;
count
--------
218085
(1 row)
relate=# select count(*) from disease;
count
--------
164597
(1 row)
relate=# select count(*) from disease_occurrences_puid;
count
--------
471915
(1 row)
relate=# select count(*) from gene_occurrences_puid;
count
--------
339347
(1 row)
relate=# explain analyze SELECT DISTINCT gene.gene_name,
gene_occurrences_puid.puid FROM gene, disease_occurrences_puid,
gene_occurrences_puid WHERE
disease_occurrences_puid.puid=gene_occurrences_puid.puid AND
gene.gene_id=gene_occurrences_puid.gene_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=342175.89..352511.77 rows=137812 width=33) (actual
time=32112.66..33139.23 rows=219435 loops=1)
-> Sort (cost=342175.89..345621.18 rows=1378118 width=33) (actual
time=32112.65..32616.14 rows=695158 loops=1)
Sort Key: gene.gene_name, gene_occurrences_puid.puid
-> Merge Join (cost=63671.50..98237.36 rows=1378118
width=33) (actual time=10061.83..17940.02 rows=695158 loops=1)
Merge Cond: ("outer".puid = "inner".puid)
-> Index Scan using disease_occpd_puid_i on
disease_occurrences_puid (cost=0.00..14538.05 rows=471915 width=4)
(actual time=0.03..3917.99 rows=471915 loops=1)
-> Sort (cost=63671.50..64519.87 rows=339347 width=29)
(actual time=10061.69..10973.64 rows=815068 loops=1)
Sort Key: gene_occurrences_puid.puid
-> Merge Join (cost=0.00..22828.18 rows=339347
width=29) (actual time=0.21..3760.59 rows=339347 loops=1)
Merge Cond: ("outer".gene_id = "inner".gene_id)
-> Index Scan using gene_pkey on gene
(cost=0.00..7668.59 rows=218085 width=21) (actual time=0.02..955.19
rows=218073 loops=1)
-> Index Scan using gene_id_puid_uni on
gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8) (actual
time=0.02..1523.81 rows=339347 loops=1)
Total runtime: 33244.81 msec
(13 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AFTER
relate=# vacuum full verbose analyze;
relate=# explain analyze SELECT DISTINCT gene.gene_name,
gene_occurrences_puid.puid FROM gene, disease_occurrences_puid,
gene_occurrences_puid WHERE
disease_occurrences_puid.puid=gene_occurrences_puid.puid AND
gene.gene_id=gene_occurrences_puid.gene_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=359069.64..369948.41 rows=145050 width=33) (actual
time=42195.60..43229.04 rows=219435 loops=1)
-> Sort (cost=359069.64..362695.90 rows=1450503 width=33) (actual
time=42195.59..42694.70 rows=695158 loops=1)
Sort Key: gene.gene_name, gene_occurrences_puid.puid
-> Merge Join (cost=63732.51..99264.24 rows=1450503
width=33) (actual time=13172.40..27973.79 rows=695158 loops=1)
Merge Cond: ("outer".puid = "inner".puid)
-> Index Scan using disease_occpd_puid_i on
disease_occurrences_puid (cost=0.00..14543.06 rows=471915 width=4)
(actual time=36.50..10916.29 rows=471915 loops=1)
-> Sort (cost=63732.51..64580.88 rows=339347 width=29)
(actual time=13126.56..14048.38 rows=815068 loops=1)
Sort Key: gene_occurrences_puid.puid
-> Merge Join (cost=0.00..22889.19 rows=339347
width=29) (actual time=58.00..6775.55 rows=339347 loops=1)
Merge Cond: ("outer".gene_id = "inner".gene_id)
-> Index Scan using gene_pkey on gene
(cost=0.00..7739.91 rows=218085 width=21) (actual time=29.00..3416.01
rows=218073
loops=1)
-> Index Scan using gene_id_puid_uni on
gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8) (actual
time=28.69..1936.83 rows=339347 loops=1)
Total runtime: 43338.94 msec
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Postgres Version: 7.3.1
CPU: 1666.767 MHz
RAM: 2070492 kB
shmmax/shmall: 1048576000
postgresql.conf:
shared_buffers: 121600
max_connections: 64
max_fsm_relations = 200
max_fsm_pages = 40000
effective_cache_size = 8000
********************************************************************
Thank you again for your interest and help!
Chantal
Stephan Szabo wrote:
> (Replying to general and performance in a hope to move this
> to performance after a couple of replies).
>
> On Wed, 22 Jan 2003, Chantal Ackermann wrote:
>
>
>>I am getting the following output from EXPLAIN, concerning a query with
>>joins. The merge uses index scans but takes too long, in my opinion. The
>>query is in fact only a part (subquery) of another one, but it is the
>>bottle neck.
>>
>>As I am quite ignorant in optimizing queries, and I have no idea where
>>to find documentation on the net on how to learn optimizing my queries,
>>I am posting this here in hope someone will give me either tips how to
>>optimize, or where to find some tutorial that could help me get along on
>>my own.
>>
>>dropping the "DISTINCT" has some effect, but I can't really do without.
>
>
> The first thing is, have you done ANALYZE recently to make sure that the
> statistics are correct and what does EXPLAIN ANALYZE give you (that will
> run the query and give the estimate and actual). Also, if you haven't
> vacuumed recently, you may want to vacuum full.
>
> How many rows are there on gene, disease and both occurrances tables?
> I'd wonder if perhaps using explicit sql join syntax (which postgres uses
> to constrain order) to join disease and disease_occurrences_puid before
> joining it to the other two would be better or worse in practice.
>
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: optimizing query
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Re: optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
@ 2003-01-23 15:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 41+ messages in thread
From: Stephan Szabo @ 2003-01-23 15:05 UTC (permalink / raw)
To: Chantal Ackermann <chantal.ackermann@biomax.de>; +Cc: pgsql-general; pgsql-performance@postgresql.org
On Thu, 23 Jan 2003, Chantal Ackermann wrote:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Postgres Version: 7.3.1
> CPU: 1666.767 MHz
> RAM: 2070492 kB
> shmmax/shmall: 1048576000
>
> postgresql.conf:
> shared_buffers: 121600
> max_connections: 64
> max_fsm_relations = 200
> max_fsm_pages = 40000
> effective_cache_size = 8000
>
> ********************************************************************
Hmm, how about how many pages are in the various tables, (do a
vacuum verbose <table> for the various tables and what is sort_mem
set to? It's picking the index scan to get the tables in sorted
order, but I wonder if that's really the best plan given it's getting
a large portion of the tables.
Hmm, what does it do if you set enable_indexscan=off; ?
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] optimizing query
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Re: optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
@ 2003-01-23 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 15:52 ` Re: [PERFORM] optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-27 00:54 ` Re: [PERFORM] optimizing query Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 41+ messages in thread
From: Tom Lane @ 2003-01-23 15:26 UTC (permalink / raw)
To: Chantal Ackermann <chantal.ackermann@biomax.de>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-general; pgsql-performance@postgresql.org
Chantal Ackermann <chantal.ackermann@biomax.de> writes:
> Unique (cost=359069.64..369948.41 rows=145050 width=33) (actual
> time=42195.60..43229.04 rows=219435 loops=1)
> -> Sort (cost=359069.64..362695.90 rows=1450503 width=33) (actual
> time=42195.59..42694.70 rows=695158 loops=1)
> Sort Key: gene.gene_name, gene_occurrences_puid.puid
> -> Merge Join (cost=63732.51..99264.24 rows=1450503
> width=33) (actual time=13172.40..27973.79 rows=695158 loops=1)
> Merge Cond: ("outer".puid = "inner".puid)
> -> Index Scan using disease_occpd_puid_i on
> disease_occurrences_puid (cost=0.00..14543.06 rows=471915 width=4)
> (actual time=36.50..10916.29 rows=471915 loops=1)
> -> Sort (cost=63732.51..64580.88 rows=339347 width=29)
> (actual time=13126.56..14048.38 rows=815068 loops=1)
> Sort Key: gene_occurrences_puid.puid
> -> Merge Join (cost=0.00..22889.19 rows=339347
> width=29) (actual time=58.00..6775.55 rows=339347 loops=1)
> Merge Cond: ("outer".gene_id = "inner".gene_id)
> -> Index Scan using gene_pkey on gene
> (cost=0.00..7739.91 rows=218085 width=21) (actual time=29.00..3416.01
> rows=218073
> loops=1)
> -> Index Scan using gene_id_puid_uni on
> gene_occurrences_puid (cost=0.00..9525.57 rows=339347 width=8) (actual
> time=28.69..1936.83 rows=339347 loops=1)
> Total runtime: 43338.94 msec
Seems like most of the time is going into the sort steps.
> postgresql.conf:
> shared_buffers: 121600
> max_connections: 64
> max_fsm_relations = 200
> max_fsm_pages = 40000
> effective_cache_size = 8000
Try increasing sort_mem.
Also, I'd back off on shared_buffers if I were you. There's no evidence
that values above a few thousand buy anything.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] optimizing query
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Re: optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-23 15:26 ` Re: [PERFORM] optimizing query Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-23 15:52 ` Chantal Ackermann <chantal.ackermann@biomax.de>
1 sibling, 0 replies; 41+ messages in thread
From: Chantal Ackermann @ 2003-01-23 15:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-general; pgsql-performance@postgresql.org
hi Stephan, hi Tom,
sort_mem was at its default: 1024. I increased it, and the query takes
even longer (~ 36 secs). I tried two different values: 4096 and 8192,
this last time I reduced the shared_buffers to 25600 (--> ~ 37 secs).
Another point is: after a vacuum, the cost would slightly increase.
would it help to cluster the index? but as I am using several indexes I
find it difficult to decide on which index to cluster.
(I paste the output from vacuum full verbose analyze)
Thanks!
Chantal
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INFO: --Relation public.disease_occurrences_puid--
INFO: Pages 2079: Changed 0, reaped 0, Empty 0, New 0; Tup 471915: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail.
Space 648/648; EndEmpty/Avail. Pages 0/1.
CPU 0.02s/0.05u sec elapsed 0.07 sec.
INFO: Index disease_occpd_puid_i: Pages 1036; Tuples 471915.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: Index disease_id_puid_uni: Pages 1297; Tuples 471915.
CPU 0.03s/0.05u sec elapsed 0.23 sec.
INFO: Rel disease_occurrences_puid: Pages: 2079 --> 2079; Tuple(s)
moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.disease_occurrences_puid
INFO: --Relation public.gene_occurrences_puid--
INFO: Pages 1495: Changed 0, reaped 0, Empty 0, New 0; Tup 339347: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail.
Space 648/648; EndEmpty/Avail. Pages 0/1.
CPU 0.01s/0.04u sec elapsed 0.05 sec.
INFO: Index gene_occpd_puid_i: Pages 746; Tuples 339347.
CPU 0.01s/0.02u sec elapsed 0.03 sec.
INFO: Index gene_id_puid_uni: Pages 934; Tuples 339347.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO: Rel gene_occurrences_puid: Pages: 1495 --> 1495; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.gene_occurrences_puid
INFO: --Relation public.disease--
INFO: Pages 1522: Changed 0, reaped 0, Empty 0, New 0; Tup 164597: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 232; Re-using: Free/Avail.
Space 56920/38388; EndEmpty/Avail. Pages 0/603.
CPU 0.00s/0.04u sec elapsed 0.04 sec.
INFO: Index disease_name_i: Pages 1076; Tuples 164597.
CPU 0.05s/0.02u sec elapsed 0.18 sec.
INFO: Index disease_pkey: Pages 364; Tuples 164597.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: Index disease_uni: Pages 1168; Tuples 164597.
CPU 0.08s/0.04u sec elapsed 0.22 sec.
INFO: Rel disease: Pages: 1522 --> 1521; Tuple(s) moved: 75.
CPU 0.00s/0.03u sec elapsed 0.04 sec.
INFO: Index disease_name_i: Pages 1077; Tuples 164597: Deleted 75.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: Index disease_pkey: Pages 364; Tuples 164597: Deleted 75.
CPU 0.01s/0.02u sec elapsed 0.02 sec.
INFO: Index disease_uni: Pages 1168; Tuples 164597: Deleted 75.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: --Relation pg_toast.pg_toast_7114632--
INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index pg_toast_7114632_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing public.disease
INFO: --Relation public.gene--
INFO: Pages 1566: Changed 0, reaped 0, Empty 0, New 0; Tup 218085: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 348; Re-using: Free/Avail.
Space 48692/25408; EndEmpty/Avail. Pages 0/365.
CPU 0.01s/0.04u sec elapsed 0.04 sec.
INFO: Index gene_pkey: Pages 481; Tuples 218085.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO: Index gene_uni: Pages 1038; Tuples 218085.
CPU 0.04s/0.01u sec elapsed 0.19 sec.
INFO: Index gene_name_uni: Pages 917; Tuples 218085.
CPU 0.06s/0.00u sec elapsed 0.15 sec.
INFO: Rel gene: Pages: 1566 --> 1564; Tuple(s) moved: 230.
CPU 0.01s/0.06u sec elapsed 0.11 sec.
INFO: Index gene_pkey: Pages 482; Tuples 218085: Deleted 230.
CPU 0.00s/0.03u sec elapsed 0.02 sec.
INFO: Index gene_uni: Pages 1041; Tuples 218085: Deleted 230.
CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO: Index gene_name_uni: Pages 918; Tuples 218085: Deleted 230.
CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO: --Relation pg_toast.pg_toast_7114653--
INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index pg_toast_7114653_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing public.gene
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] optimizing query
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Re: optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-23 15:26 ` Re: [PERFORM] optimizing query Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-27 00:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 41+ messages in thread
From: Bruce Momjian @ 2003-01-27 00:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chantal Ackermann <chantal.ackermann@biomax.de>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-general; pgsql-performance@postgresql.org
Tom Lane wrote:
> > postgresql.conf:
> > shared_buffers: 121600
> > max_connections: 64
> > max_fsm_relations = 200
> > max_fsm_pages = 40000
> > effective_cache_size = 8000
>
> Try increasing sort_mem.
>
> Also, I'd back off on shared_buffers if I were you. There's no evidence
> that values above a few thousand buy anything.
Increasing shared_buffers above several thousand will only be a win if
your entire working set will fit in the larger buffer pool, but didn't
in the previous size. If you working set is smaller or larger than
that, pushing it above several thousand isn't a win. Is that a more
definitive answer?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 41+ messages in thread
* Optimizing query
@ 2003-11-19 10:41 Uros <uros@sir-mag.com>
2003-11-19 11:44 ` Re: Optimizing query Matthew Lunnon <mlunnon@rwa-net.co.uk>
2003-11-19 12:21 ` Re: Optimizing query Peter Eisentraut <peter_e@gmx.net>
2003-11-19 12:23 ` Re: Optimizing query Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
0 siblings, 3 replies; 41+ messages in thread
From: Uros @ 2003-11-19 10:41 UTC (permalink / raw)
To: pgsql-general
Hello!
I have some trouble getting good results from my query.
here is structure
stat_views
id | integer
id_zone | integer
created | timestamp
I have btree index on created and also id and there is 1633832 records in
that table
First of all I have to manualy set seq_scan to OFF because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
-> Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60 rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
-> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
--
Best regards,
Uros mailto:uros@sir-mag.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
@ 2003-11-19 11:44 ` Matthew Lunnon <mlunnon@rwa-net.co.uk>
2 siblings, 0 replies; 41+ messages in thread
From: Matthew Lunnon @ 2003-11-19 11:44 UTC (permalink / raw)
To: Uros <uros@sir-mag.com>; pgsql-general
Do something like:
CREATE OR REPLACE FUNCTION my_date_part( timestamp) RETURNS DOUBLE precision AS '
DECLARE
mydate ALIAS FOR $1;
BEGIN
return date_part( ''day'', mydate );
END;' LANGUAGE 'plpgsql' IMMUTABLE;
create index idx_tmp on stat_views( my_date_part( created ) );
or add an extra date_part column to your table which pre-calculates date_part('day', created) and put an index on this.
Cheers
Matthew
--
----- Original Message -----
From: Uros
To: pgsql-general@postgresql.org
Sent: Wednesday, November 19, 2003 10:41 AM
Subject: [GENERAL] Optimizing query
Hello!
I have some trouble getting good results from my query.
here is structure
stat_views
id | integer
id_zone | integer
created | timestamp
I have btree index on created and also id and there is 1633832 records in
that table
First of all I have to manualy set seq_scan to OFF because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
-> Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60 rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
-> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
--
Best regards,
Uros mailto:uros@sir-mag.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
_____________________________________________________________________
This e-mail has been scanned for viruses by MCI's Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.mci.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
@ 2003-11-19 12:21 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 41+ messages in thread
From: Peter Eisentraut @ 2003-11-19 12:21 UTC (permalink / raw)
To: Uros <uros@sir-mag.com>; +Cc: pgsql-general
Uros writes:
> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
> Filter: (date_part('day'::text, created) = 18::double precision)
Create an index on date_part('day', created). In 7.3 and earlier you need
to create a wrapper function and index that, in 7.4 you can index
arbitrarz expressions directly. The documentation contains more
information about that.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
@ 2003-11-19 12:23 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 12:25 ` Re: Optimizing query Uros <uros@sir-mag.com>
2003-11-19 13:44 ` Re: Optimizing query Rob Sell <lists@facnd.com>
2 siblings, 2 replies; 41+ messages in thread
From: Shridhar Daithankar @ 2003-11-19 12:23 UTC (permalink / raw)
To: Uros <uros@sir-mag.com>; +Cc: pgsql-general
Uros wrote:
> Hello!
>
> I have some trouble getting good results from my query.
>
> here is structure
>
> stat_views
> id | integer
> id_zone | integer
> created | timestamp
>
>
> I have btree index on created and also id and there is 1633832 records in
> that table
>
> First of all I have to manualy set seq_scan to OFF because I always get
> seq_scan. When i set it to off my explain show:
>
> explain SELECT count(*) as views FROM stat_views WHERE id = 12;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------
> Aggregate (cost=122734.86..122734.86 rows=1 width=0)
> -> Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60 rows=40904 width=0)
> Index Cond: (id = 12)
>
> But what I need is to count views for some day, so I use
>
> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
> Filter: (date_part('day'::text, created) = 18::double precision)
>
>
> How can I make this to use index and speed the query. Now it takes about 12
> seconds.
Can you post explain analyze for the same?
Shridhar
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
2003-11-19 12:23 ` Re: Optimizing query Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
@ 2003-11-19 12:25 ` Uros <uros@sir-mag.com>
1 sibling, 0 replies; 41+ messages in thread
From: Uros @ 2003-11-19 12:25 UTC (permalink / raw)
To: pgsql-general
Hello Shridhar,
I use Matthew's solution and it works. Query takes only half a second. I
didn't know that i can index function to.
Thanks
Uros
Wednesday, November 19, 2003, 1:23:26 PM, you wrote:
SD> Uros wrote:
>> Hello!
>>
>> I have some trouble getting good results from my query.
>>
>> here is structure
>>
>> stat_views
>> id | integer
>> id_zone | integer
>> created | timestamp
>>
>>
>> I have btree index on created and also id and there is 1633832 records in
>> that table
>>
>> First of all I have to manualy set seq_scan to OFF because I always get
>> seq_scan. When i set it to off my explain show:
>>
>> explain SELECT count(*) as views FROM stat_views WHERE id = 12;
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------
>> Aggregate (cost=122734.86..122734.86 rows=1 width=0)
>> -> Index Scan using stat_views_id_idx on stat_views
>> (cost=0.00..122632.60 rows=40904 width=0)
>> Index Cond: (id = 12)
>>
>> But what I need is to count views for some day, so I use
>>
>> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
>>
>> QUERY PLAN
>> ------------------------------------------------------------------------------------
>> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
>> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
>> Filter: (date_part('day'::text, created) = 18::double precision)
>>
>>
>> How can I make this to use index and speed the query. Now it takes about 12
>> seconds.
SD> Can you post explain analyze for the same?
SD> Shridhar
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
2003-11-19 12:23 ` Re: Optimizing query Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
@ 2003-11-19 13:44 ` Rob Sell <lists@facnd.com>
2003-11-19 16:38 ` Re: Optimizing query Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 41+ messages in thread
From: Rob Sell @ 2003-11-19 13:44 UTC (permalink / raw)
To: pgsql-general
Greetings all,
Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the
following error.
psql: relocation error: psql: undefined symbol: get_progname
Any ideas out there?
Rob
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Shridhar Daithankar
Sent: Wednesday, November 19, 2003 6:23 AM
To: Uros
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Optimizing query
Uros wrote:
> Hello!
>
> I have some trouble getting good results from my query.
>
> here is structure
>
> stat_views
> id | integer
> id_zone | integer
> created | timestamp
>
>
> I have btree index on created and also id and there is 1633832 records in
> that table
>
> First of all I have to manualy set seq_scan to OFF because I always get
> seq_scan. When i set it to off my explain show:
>
> explain SELECT count(*) as views FROM stat_views WHERE id = 12;
> QUERY PLAN
>
----------------------------------------------------------------------------
------------------------
> Aggregate (cost=122734.86..122734.86 rows=1 width=0)
> -> Index Scan using stat_views_id_idx on stat_views
(cost=0.00..122632.60 rows=40904 width=0)
> Index Cond: (id = 12)
>
> But what I need is to count views for some day, so I use
>
> explain SELECT count(*) as views FROM stat_views WHERE date_part('day',
created) = 18;
>
> QUERY PLAN
>
----------------------------------------------------------------------------
--------
> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984
width=0)
> Filter: (date_part('day'::text, created) = 18::double precision)
>
>
> How can I make this to use index and speed the query. Now it takes about
12
> seconds.
Can you post explain analyze for the same?
Shridhar
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
2003-11-19 12:23 ` Re: Optimizing query Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 13:44 ` Re: Optimizing query Rob Sell <lists@facnd.com>
@ 2003-11-19 16:38 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 41+ messages in thread
From: Bruce Momjian @ 2003-11-19 16:38 UTC (permalink / raw)
To: lists@facnd.com; +Cc: pgsql-general
Rob Sell wrote:
> Greetings all,
>
> Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the
> following error.
>
> psql: relocation error: psql: undefined symbol: get_progname
>
> Any ideas out there?
You have an old copy of the library or binaries around somewhere.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 41+ messages in thread
* Optimizing query
@ 2005-08-15 08:38 Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 09:13 ` Re: Optimizing query Richard Huxton <dev@archonet.com>
2005-08-15 10:11 ` Re: Optimizing query Dennis Bjorklund <db@zigo.dhs.org>
0 siblings, 2 replies; 41+ messages in thread
From: Poul Møller Hansen @ 2005-08-15 08:38 UTC (permalink / raw)
To: pgsql-general
I have a problem creating a usable index for the following simple query:
SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
id is a serial, so the query is to find the latest entry to a given node
and id is the primary key.
The table contains around 1 million records and the query takes around 2
seconds.
I have tried to make an index on node and also on both id & node, but is
doesn't lower the
query time.
What am I doing wrong ?
Thanks,
Poul
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2005-08-15 08:38 Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
@ 2005-08-15 09:13 ` Richard Huxton <dev@archonet.com>
2005-08-15 09:46 ` Re: Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
1 sibling, 1 reply; 41+ messages in thread
From: Richard Huxton @ 2005-08-15 09:13 UTC (permalink / raw)
To: Poul Møller Hansen <freebsd@pbnet.dk>; +Cc: pgsql-general
Poul Møller Hansen wrote:
> I have a problem creating a usable index for the following simple query:
> SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
>
> id is a serial, so the query is to find the latest entry to a given node
> and id is the primary key.
You're not necessarily getting the latest entry, just the one with the
highest "id". Sequences guarantee uniqueness but if you have concurrent
inserts not necessarily ordering.
> The table contains around 1 million records and the query takes around 2
> seconds.
Well, you don't say how many different values for "node" there are, nor
how many rows you would expect where node='10'.
> I have tried to make an index on node and also on both id & node, but is
> doesn't lower the query time.
Difficult to say what's happening since you don't supply any EXPLAIN
ANALYSE output.
However, if you have an index on (node,id) you might want to try:
SELECT ... ORDER BY node DESC, id DESC LIMIT 1;
That way the "ORDER BY" part clearly tells the planner that a
reverse-order on your index will be useful.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2005-08-15 08:38 Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 09:13 ` Re: Optimizing query Richard Huxton <dev@archonet.com>
@ 2005-08-15 09:46 ` Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 13:46 ` Re: Optimizing query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 41+ messages in thread
From: Poul Møller Hansen @ 2005-08-15 09:46 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-general
>> I have a problem creating a usable index for the following simple query:
>> SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
>>
>> id is a serial, so the query is to find the latest entry to a given
>> node and id is the primary key.
>
>
> You're not necessarily getting the latest entry, just the one with the
> highest "id". Sequences guarantee uniqueness but if you have
> concurrent inserts not necessarily ordering.
>
Right you are, but I have no concurrent inserts from the same node.
>
> Difficult to say what's happening since you don't supply any EXPLAIN
> ANALYSE output.
>
> However, if you have an index on (node,id) you might want to try:
> SELECT ... ORDER BY node DESC, id DESC LIMIT 1;
> That way the "ORDER BY" part clearly tells the planner that a
> reverse-order on your index will be useful.
>
Thanks a lot, that did the trick !
explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY id
DESC LIMIT 1
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..764.00 rows=1 width=246) (actual
time=1874.890..1874.896 rows=1 loops=1)
-> Index Scan Backward using table_pkey on table
(cost=0.00..4347913.94 rows=5691 width=246) (actual
time=1874.867..1874.867 rows=1 loops=1)
Filter: ((node)::text = '10'::text)
Total runtime: 1875.111 ms
explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY node,
id DESC LIMIT 1
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=22638.36..22638.36 rows=1 width=246) (actual
time=3.001..3.007 rows=1 loops=1)
-> Sort (cost=22638.36..22652.59 rows=5691 width=246) (actual
time=2.984..2.984 rows=1 loops=1)
Sort Key: node, id
-> Index Scan using node_date on table (cost=0.00..21898.65
rows=5691 width=246) (actual time=0.077..1.852 rows=62 loops=1)
Index Cond: ((node)::text = '10'::text)
Total runtime: 3.127 ms
Poul
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2005-08-15 08:38 Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 09:13 ` Re: Optimizing query Richard Huxton <dev@archonet.com>
2005-08-15 09:46 ` Re: Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
@ 2005-08-15 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-15 14:54 ` Re: Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
0 siblings, 1 reply; 41+ messages in thread
From: Tom Lane @ 2005-08-15 13:46 UTC (permalink / raw)
To: Poul Møller Hansen <freebsd@pbnet.dk>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-general
=?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= <freebsd@pbnet.dk> writes:
> explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY node,
> id DESC LIMIT 1
> QUERY
> PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=22638.36..22638.36 rows=1 width=246) (actual
> time=3.001..3.007 rows=1 loops=1)
> -> Sort (cost=22638.36..22652.59 rows=5691 width=246) (actual
> time=2.984..2.984 rows=1 loops=1)
> Sort Key: node, id
> -> Index Scan using node_date on table (cost=0.00..21898.65
> rows=5691 width=246) (actual time=0.077..1.852 rows=62 loops=1)
> Index Cond: ((node)::text = '10'::text)
> Total runtime: 3.127 ms
You're not there yet: you want what Richard said, namely
explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY node DESC,
id DESC LIMIT 1
There shouldn't be any Sort in the plan, just the indexscan and Limit.
The plan above is going to suck if there are a lot of rows with node = '10'.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2005-08-15 08:38 Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 09:13 ` Re: Optimizing query Richard Huxton <dev@archonet.com>
2005-08-15 09:46 ` Re: Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 13:46 ` Re: Optimizing query Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-15 14:54 ` Poul Møller Hansen <freebsd@pbnet.dk>
0 siblings, 0 replies; 41+ messages in thread
From: Poul Møller Hansen @ 2005-08-15 14:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-general
>
> You're not there yet: you want what Richard said, namely
>
I realized that it wasn't optimal for all nodes, namely those with a lot
of rows.
So you are absolutely right, I followed the suggestion of Richard and it
works perfect.
Thank you all, I learned a lesson of indexes today...
Poul
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2005-08-15 08:38 Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
@ 2005-08-15 10:11 ` Dennis Bjorklund <db@zigo.dhs.org>
1 sibling, 0 replies; 41+ messages in thread
From: Dennis Bjorklund @ 2005-08-15 10:11 UTC (permalink / raw)
To: Poul Møller Hansen <freebsd@pbnet.dk>; +Cc: pgsql-general
On Mon, 15 Aug 2005, Poul Møller Hansen wrote:
> I have a problem creating a usable index for the following simple query:
> SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
>
> id is a serial, so the query is to find the latest entry to a given node
> and id is the primary key.
> The table contains around 1 million records and the query takes around 2
> seconds.
>
> I have tried to make an index on node and also on both id & node, but is
> doesn't lower the
> query time.
Try to make an index on (node,id) and write the query as:
SELECT * FROM my.table WHERE node = '10' ORDER BY node desc, id desc LIMIT 1;
Then i'm pretty sure it will use that index.
--
/Dennis Björklund
^ permalink raw reply [nested|flat] 41+ messages in thread
* Optimizing query
@ 2010-11-24 09:59 =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
0 siblings, 0 replies; 41+ messages in thread
From: pasman pasmañski @ 2010-11-24 09:59 UTC (permalink / raw)
To: pgsql-general
Hello.
I have a query which works a bit slow.
It's runned on desktop computer: AMD Athlon X2 2GHz , Win Xp sp2, 1GB ram.
Postgres 8.4.5 with some changes in config:
shared_buffers = 200MB # min 128kB
# (change requires restart)
temp_buffers = 8MB # min 800kB
work_mem = 12MB # min 64kB
maintenance_work_mem = 32MB # min 1MB
Indexes in table "NumeryA":
"NTA", "NKA", "KodB³êdu", "Plik" primary key
"DataPliku", "KodB³êdu" index dp_kb
"NKA", "NTA" index nka_nta
Indexes in table "Rejestr stacji do naprawy":
"LP" - primary key
"Numer kierunkowy", substr("Numer stacji"::text, 1, 5) - index "3"
"Data weryfikacji" - index "Data weryfikacji_1"
"Numer kierunkowy", "Numer stacji", "Data odrzucania bilingu z
Serat" - index "Powtórzenia"
---------------------
Query is:
----------------------
SELECT
A."NKA",
A."NTA",
Min("Po³±czeniaMin") || ',' || Max("Po³±czeniaMax") AS "Biling",
Sum("Ile")::text AS "Ilo¶æ CDR",
R."LP"::text AS "Sprawa",
(R."Osoba weryfikuj±ca") AS "Osoba",
to_char(min("Warto¶æ"),'FM9999990D00') AS "Warto¶æ po kontroli",
max(R."Kontrola po naprawie w Serat - CDR")::text AS "CDR po kontroli",
min(A."KodB³êdu")::text AS KodB³êdu,
Max(to_char(R."Data kontroli",'YYYY-MM-DD')) AS "Ostatnia Kontrola"
, max("Skutek wprowadzenia b³ednej ewidencji w Serat") as "Skutek"
, sum(www.a_biling_070("NRB"))::text
, sum(www.a_biling_darmowy("NRB"))::text
FROM
(SELECT "NumeryA".*
FROM ONLY "NumeryA"
WHERE "DataPliku" >= current_date-4*30 and "KodB³êdu"=74::text
) AS A
LEFT JOIN
(SELECT * FROM "Rejestr stacji do naprawy"
WHERE "Data weryfikacji" >= current_date-4*30
) AS R
ON
A."NKA" = R."Numer kierunkowy"
and substr(A."NTA",1,5) = substr(R."Numer stacji",1,5)
and A."NTA" like R."Numer stacji"
GROUP BY R."Osoba weryfikuj±ca",R."LP",A."NKA", A."NTA"
ORDER BY Sum("Ile") DESC
LIMIT 5000
----------------------
Explain analyze:
----------------------
"Limit (cost=30999.84..31012.34 rows=5000 width=149) (actual
time=7448.483..7480.094 rows=5000 loops=1)"
" -> Sort (cost=30999.84..31073.19 rows=29341 width=149) (actual
time=7448.475..7459.663 rows=5000 loops=1)"
" Sort Key: (sum("NumeryA"."Ile"))"
" Sort Method: top-N heapsort Memory: 1488kB"
" -> GroupAggregate (cost=11093.77..29050.46 rows=29341
width=149) (actual time=4700.654..7377.762 rows=14225 loops=1)"
" -> Sort (cost=11093.77..11167.12 rows=29341
width=149) (actual time=4699.587..4812.776 rows=46732 loops=1)"
" Sort Key: "Rejestr stacji do naprawy"."Osoba
weryfikuj±ca", "Rejestr stacji do naprawy"."LP", "NumeryA"."NKA",
"NumeryA"."NTA""
" Sort Method: quicksort Memory: 9856kB"
" -> Merge Left Join (cost=8297.99..8916.58
rows=29341 width=149) (actual time=2931.449..3735.876 rows=46732
loops=1)"
" Merge Cond: ((("NumeryA"."NKA")::text =
("Rejestr stacji do naprawy"."Numer kierunkowy")::text) AND
((substr(("NumeryA"."NTA")::text, 1, 5)) = (substr(("Rejestr stacji do
naprawy"."Numer stacji")::text, 1, 5))))"
" Join Filter: (("NumeryA"."NTA")::text ~~
("Rejestr stacji do naprawy"."Numer stacji")::text)"
" -> Sort (cost=6062.18..6135.53 rows=29341
width=95) (actual time=2131.297..2241.303 rows=46694 loops=1)"
" Sort Key: "NumeryA"."NKA",
(substr(("NumeryA"."NTA")::text, 1, 5))"
" Sort Method: quicksort Memory: 7327kB"
" -> Bitmap Heap Scan on "NumeryA"
(cost=1502.09..3884.98 rows=29341 width=95) (actual
time=282.570..1215.355 rows=46694 loops=1)"
" Recheck Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodB³êdu")::text = '74'::text))"
" -> Bitmap Index Scan on dp_kb
(cost=0.00..1494.75 rows=29341 width=0) (actual time=281.991..281.991
rows=46694 loops=1)"
" Index Cond: (("DataPliku"
>= (('now'::text)::date - 120)) AND (("KodB³êdu")::text =
'74'::text))"
" -> Sort (cost=2235.82..2285.03 rows=19684
width=64) (actual time=800.101..922.463 rows=54902 loops=1)"
" Sort Key: "Rejestr stacji do
naprawy"."Numer kierunkowy", (substr(("Rejestr stacji do
naprawy"."Numer stacji")::text, 1, 5))"
" Sort Method: quicksort Memory: 3105kB"
" -> Seq Scan on "Rejestr stacji do
naprawy" (cost=0.00..831.88 rows=19684 width=64) (actual
time=2.118..361.463 rows=19529 loops=1)"
" Filter: ("Data weryfikacji" >=
(('now'::text)::date - 120))"
"Total runtime: 7495.697 ms"
---------------------------------
How to make it faster ?
------------
pasman
^ permalink raw reply [nested|flat] 41+ messages in thread
* Optimizing query?
@ 2013-01-30 11:08 wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
0 siblings, 1 reply; 41+ messages in thread
From: wolfgang@noten5.maas-noten.de @ 2013-01-30 11:08 UTC (permalink / raw)
To: pgsql-general
Hi,
I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, etc
The table has an index on that column.
The second table is, typically, much smaller
select .... from tab1, tab2 where tab1.code = tab2.code;
This works fine and fast.
Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D in the
big table and want them to match XY423, GF55 in the second table
Variants I have tried
select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z])');
both take an enormous time. In the better case that I can subset (e.g. all candidates in table 2
share initial "AX") I get back to manageable times by adding
and tab1.code ~ '^AX'
into the recipe. Actual runtime with about a million entries in tab1 and 800 entries in tab2
is about 40 seconds.
Regards
Wolfgang Hamann
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
@ 2013-01-31 07:07 ` Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: Vincent Veyron @ 2013-01-31 07:07 UTC (permalink / raw)
To: wolfgang@noten5.maas-noten.de; +Cc: pgsql-general
Le mercredi 30 janvier 2013 Ã 11:08 +0000, wolfgang@noten5.maas-noten.de
a écrit :
> Hi,
>
> I am trying to match items from 2 tables based on a common string.
> One is a big table which has one column with entries like XY123, ABC44, etc
> The table has an index on that column.
> The second table is, typically, much smaller
>
> select .... from tab1, tab2 where tab1.code = tab2.code;
>
> This works fine and fast.
> Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D in the
> big table and want them to match XY423, GF55 in the second table
>
> Variants I have tried
>
> select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
> select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z])');
>
Have you tried the substring function?
select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =
tab2.code
> both take an enormous time. In the better case that I can subset (e.g. all candidates in table 2
> share initial "AX") I get back to manageable times by adding
> and tab1.code ~ '^AX'
> into the recipe. Actual runtime with about a million entries in tab1 and 800 entries in tab2
> is about 40 seconds.
>
> Regards
> Wolfgang Hamann
>
>
>
>
>
>
--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
@ 2013-01-31 08:49 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 10:03 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
0 siblings, 2 replies; 41+ messages in thread
From: Pavel Stehule @ 2013-01-31 08:49 UTC (permalink / raw)
To: Vincent Veyron <vv.lists@wanadoo.fr>; +Cc: wolfgang@noten5.maas-noten.de; pgsql-general
2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:
> Le mercredi 30 janvier 2013 Ã 11:08 +0000, wolfgang@noten5.maas-noten.de
> a écrit :
>> Hi,
>>
>> I am trying to match items from 2 tables based on a common string.
>> One is a big table which has one column with entries like XY123, ABC44, etc
>> The table has an index on that column.
>> The second table is, typically, much smaller
>>
>> select .... from tab1, tab2 where tab1.code = tab2.code;
>>
>> This works fine and fast.
>> Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D in the
>> big table and want them to match XY423, GF55 in the second table
>>
>> Variants I have tried
>>
>> select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
>> select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z])');
>>
>
> Have you tried the substring function?
>
> select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =
> tab2.code
>
>
>> both take an enormous time. In the better case that I can subset (e.g. all candidates in table 2
>> share initial "AX") I get back to manageable times by adding
>> and tab1.code ~ '^AX'
>> into the recipe. Actual runtime with about a million entries in tab1 and 800 entries in tab2
>> is about 40 seconds.
any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.
You can try use a functional index.
create index on tab2 ((substring(tab1.code from 1 for 5))
Regards
Pavel Stehule
>>
>> Regards
>> Wolfgang Hamann
>>
>>
>>
>>
>>
>>
>
> --
> Salutations, Vincent Veyron
> http://marica.fr/site/demonstration
> Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
@ 2013-01-31 10:03 ` Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 10:06 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 41+ messages in thread
From: Vincent Veyron @ 2013-01-31 10:03 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general
Le jeudi 31 janvier 2013 à 09:49 +0100, Pavel Stehule a écrit :
> any join where result is related to some function result can be very
> slow, because estimation will be out and any repeated function
> evaluation is just expensive.
>
Hi Pavel,
Thank you for the correction. Since we're at it, I have a question
regarding functions in a query :
Suppose I have a query of the form
SELECT my_function(column_1), column_2
FROM my_table
GROUP BY my_function(column_1)
ORDER BY my_function(column_1);
where my_function is a user defined function.
How many times is the function computed?
--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 10:03 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
@ 2013-01-31 10:06 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 14:00 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
0 siblings, 1 reply; 41+ messages in thread
From: Pavel Stehule @ 2013-01-31 10:06 UTC (permalink / raw)
To: Vincent Veyron <vv.lists@wanadoo.fr>; +Cc: pgsql-general
2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:
> Le jeudi 31 janvier 2013 à 09:49 +0100, Pavel Stehule a écrit :
>
>> any join where result is related to some function result can be very
>> slow, because estimation will be out and any repeated function
>> evaluation is just expensive.
>>
>
> Hi Pavel,
>
> Thank you for the correction. Since we're at it, I have a question
> regarding functions in a query :
>
> Suppose I have a query of the form
>
> SELECT my_function(column_1), column_2
> FROM my_table
> GROUP BY my_function(column_1)
> ORDER BY my_function(column_1);
>
> where my_function is a user defined function.
>
> How many times is the function computed?
if function is stable or immutable, then once per row
Pavel
>
>
>
>
> --
> Salutations, Vincent Veyron
> http://marica.fr/site/demonstration
> Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 10:03 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 10:06 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
@ 2013-01-31 14:00 ` Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 14:09 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: Vincent Veyron @ 2013-01-31 14:00 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general
Le jeudi 31 janvier 2013 à 11:06 +0100, Pavel Stehule a écrit :
> 2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:
> >
> > Suppose I have a query of the form
> >
> > SELECT my_function(column_1), column_2
> > FROM my_table
> > GROUP BY my_function(column_1)
> > ORDER BY my_function(column_1);
> >
> > where my_function is a user defined function.
> >
> > How many times is the function computed?
>
> if function is stable or immutable, then once per row
>
In this post (watch for line-wrap) :
http://www.postgresql.org/message-id/CAFj8pRAdYL1-hCxH
+QSZQKHt9YnoaOiGkfX4cNc9mzUTimcs1w@mail.gmail.com
you wrote that it is usually better not to mark SQL functions (as
opposed to plpgsql functions).
So should I mark SQL functions stable/immutable if I use them in a query
like the one above, or is it unnecessary?
--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 10:03 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 10:06 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 14:00 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
@ 2013-01-31 14:09 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 41+ messages in thread
From: Pavel Stehule @ 2013-01-31 14:09 UTC (permalink / raw)
To: Vincent Veyron <vv.lists@wanadoo.fr>; +Cc: pgsql-general
2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:
> Le jeudi 31 janvier 2013 à 11:06 +0100, Pavel Stehule a écrit :
>> 2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:
>> >
>> > Suppose I have a query of the form
>> >
>> > SELECT my_function(column_1), column_2
>> > FROM my_table
>> > GROUP BY my_function(column_1)
>> > ORDER BY my_function(column_1);
>> >
>> > where my_function is a user defined function.
>> >
>> > How many times is the function computed?
>>
>> if function is stable or immutable, then once per row
>>
>
> In this post (watch for line-wrap) :
>
> http://www.postgresql.org/message-id/CAFj8pRAdYL1-hCxH
> +QSZQKHt9YnoaOiGkfX4cNc9mzUTimcs1w@mail.gmail.com
>
> you wrote that it is usually better not to mark SQL functions (as
> opposed to plpgsql functions).
>
> So should I mark SQL functions stable/immutable if I use them in a query
> like the one above, or is it unnecessary?
>
It should not be marked
Regards
Pavel
>
> --
> Salutations, Vincent Veyron
> http://marica.fr/site/demonstration
> Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
@ 2013-01-31 18:29 ` hamann.w@t-online.de
2013-02-01 05:50 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-02-03 05:33 ` Re: Optimizing query? Jasen Betts <jasen@xnet.co.nz>
1 sibling, 2 replies; 41+ messages in thread
From: hamann.w@t-online.de @ 2013-01-31 18:29 UTC (permalink / raw)
To: pgsql-general
Pavel Stehlule wrote:
>> >> Hi,
>> >>
>> >> I am trying to match items from 2 tables based on a common string.
>> >> One is a big table which has one column with entries like XY123, ABC44, =
>> etc
>> >> The table has an index on that column.
>> >> The second table is, typically, much smaller
>> >>
>> >> select .... from tab1, tab2 where tab1.code =3D tab2.code;
>> >>
>> >> This works fine and fast.
>> >> Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D=
>> in the
>> >> big table and want them to match XY423, GF55 in the second table
>> >>
>> >> Variants I have tried
>> >>
>> >> select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
>> >> select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z=
>> ])');
>> >>
>> >
>> > Have you tried the substring function?
>> >
>> > select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =3D
>> > tab2.code
>> >
Hi Pavel, it was just by chance that a fixed size substring would match the
data at hand. It is more common to have a digit/letter (or vice versa) boundary
or a hyphen there
>> >
>> >> both take an enormous time. In the better case that I can subset (e.g. a=
>> ll candidates in table 2
>> >> share initial "AX") I get back to manageable times by adding
>> >> and tab1.code ~ '^AX'
>> >> into the recipe. Actual runtime with about a million entries in tab1 and=
>> 800 entries in tab2
>> >> is about 40 seconds.
>>
>> any join where result is related to some function result can be very
>> slow, because estimation will be out and any repeated function
>> evaluation is just expensive.
>>
I see the problem since obviously every the ~ operator with a non-constant
pattern is constantly recompiling the pattern.
I wonder whether it would be possible to invent a prefix-match operator that approaches
the performance of string equality. I noted in the past (not sure whether anything
has changed in regex matching) that a constant leading part of regex would improve
performance, i.e. use an index scan to select possible candidates.
>> You can try use a functional index.
>>
>> create index on tab2 ((substring(tab1.code from 1 for 5))
>>
What kind of trick is that - mixing two tables into a functional index?
What would the exact syntax be for that?
Regards
Wolfgang Hamann
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
@ 2013-02-01 05:50 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-02-02 09:17 ` Re: Optimizing query? hamann.w@t-online.de
1 sibling, 1 reply; 41+ messages in thread
From: Pavel Stehule @ 2013-02-01 05:50 UTC (permalink / raw)
To: hamann.w@t-online.de; +Cc: pgsql-general
Hello
2013/1/31 <hamann.w@t-online.de>:
>
> Pavel Stehlule wrote:
>
>>> >> Hi,
>>> >>
>>> >> I am trying to match items from 2 tables based on a common string.
>>> >> One is a big table which has one column with entries like XY123, ABC44, =
>>> etc
>>> >> The table has an index on that column.
>>> >> The second table is, typically, much smaller
>>> >>
>>> >> select .... from tab1, tab2 where tab1.code =3D tab2.code;
>>> >>
>>> >> This works fine and fast.
>>> >> Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D=
>>> in the
>>> >> big table and want them to match XY423, GF55 in the second table
>>> >>
>>> >> Variants I have tried
>>> >>
>>> >> select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
>>> >> select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z=
>>> ])');
>>> >>
>>> >
>>> > Have you tried the substring function?
>>> >
>>> > select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =3D
>>> > tab2.code
>>> >
>
> Hi Pavel, it was just by chance that a fixed size substring would match the
> data at hand. It is more common to have a digit/letter (or vice versa) boundary
> or a hyphen there
>
>>> >
>>> >> both take an enormous time. In the better case that I can subset (e.g. a=
>>> ll candidates in table 2
>>> >> share initial "AX") I get back to manageable times by adding
>>> >> and tab1.code ~ '^AX'
>>> >> into the recipe. Actual runtime with about a million entries in tab1 and=
>>> 800 entries in tab2
>>> >> is about 40 seconds.
>>>
>>> any join where result is related to some function result can be very
>>> slow, because estimation will be out and any repeated function
>>> evaluation is just expensive.
>>>
> I see the problem since obviously every the ~ operator with a non-constant
> pattern is constantly recompiling the pattern.
>
> I wonder whether it would be possible to invent a prefix-match operator that approaches
> the performance of string equality. I noted in the past (not sure whether anything
> has changed in regex matching) that a constant leading part of regex would improve
> performance, i.e. use an index scan to select possible candidates.
>
>>> You can try use a functional index.
>>>
>>> create index on tab2 ((substring(tab1.code from 1 for 5))
>>>
>
> What kind of trick is that - mixing two tables into a functional index?
it is not possible - you can do some auxiliary table and creating
indexes over this table
but maybe https://github.com/dimitri/prefix can help
Regards
Pavel
> What would the exact syntax be for that?
>
> Regards
> Wolfgang Hamann
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
2013-02-01 05:50 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
@ 2013-02-02 09:17 ` hamann.w@t-online.de
2013-02-02 09:25 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: hamann.w@t-online.de @ 2013-02-02 09:17 UTC (permalink / raw)
To: pgsql-general
Pavel Stehule wrote:
>>
>> but maybe https://github.com/dimitri/prefix can help
>>
Hi Pavel,
thanks - this works perfect. However, it does not seem to play well
with the optimizer, so I ended up with
select all candidates into a temp table using prefix operator
apply all other conditions by joining that temp table to original ones
Regards
Wolfgang
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
2013-02-01 05:50 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-02-02 09:17 ` Re: Optimizing query? hamann.w@t-online.de
@ 2013-02-02 09:25 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-02-02 10:11 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: Pavel Stehule @ 2013-02-02 09:25 UTC (permalink / raw)
To: hamann.w@t-online.de; +Cc: pgsql-general
2013/2/2 <hamann.w@t-online.de>:
> Pavel Stehule wrote:
>
>>>
>>> but maybe https://github.com/dimitri/prefix can help
>>>
>
> Hi Pavel,
>
> thanks - this works perfect. However, it does not seem to play well
> with the optimizer, so I ended up with
>
> select all candidates into a temp table using prefix operator
> apply all other conditions by joining that temp table to original ones
>
you can send proposals to enhancing to Dimitry - He will be happy :)
Regards
Pavel
> Regards
> Wolfgang
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
2013-02-01 05:50 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-02-02 09:17 ` Re: Optimizing query? hamann.w@t-online.de
2013-02-02 09:25 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
@ 2013-02-02 10:11 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 41+ messages in thread
From: Pavel Stehule @ 2013-02-02 10:11 UTC (permalink / raw)
To: hamann.w@t-online.de; +Cc: pgsql-general
2013/2/2 Pavel Stehule <pavel.stehule@gmail.com>:
> 2013/2/2 <hamann.w@t-online.de>:
>> Pavel Stehule wrote:
>>
>>>>
>>>> but maybe https://github.com/dimitri/prefix can help
>>>>
>>
>> Hi Pavel,
>>
>> thanks - this works perfect. However, it does not seem to play well
>> with the optimizer, so I ended up with
>>
>> select all candidates into a temp table using prefix operator
>> apply all other conditions by joining that temp table to original ones
>>
seriously - it is typical solution - and it is great so PostgreSQL
help to you :)
Regards
Pavel
>
> you can send proposals to enhancing to Dimitry - He will be happy :)
>
> Regards
>
> Pavel
>
>> Regards
>> Wolfgang
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query?
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
@ 2013-02-03 05:33 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 41+ messages in thread
From: Jasen Betts @ 2013-02-03 05:33 UTC (permalink / raw)
To: pgsql-general
On 2013-01-31, hamann.w@t-online.de <hamann.w@t-online.de> wrote:
>
> Pavel Stehlule wrote:
>
>>> >> Hi,
>>> >>
>>> >> I am trying to match items from 2 tables based on a common string.
>>> >> One is a big table which has one column with entries like XY123, ABC44, =
>>> etc
>>> >> The table has an index on that column.
>>> >> The second table is, typically, much smaller
>>> >>
>>> >> select .... from tab1, tab2 where tab1.code =3D tab2.code;
>>> >>
>>> >> This works fine and fast.
>>> >> Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D=
>>> in the
>>> >> big table and want them to match XY423, GF55 in the second table
>>> >>
>>> >> Variants I have tried
>>> >>
>>> >> select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
>>> >> select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z=
>>> ])');
>>> >>
>>> >
>>> > Have you tried the substring function?
>>> >
>>> > select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =3D
>>> > tab2.code
>>> >
>
> Hi Pavel, it was just by chance that a fixed size substring would match the
> data at hand. It is more common to have a digit/letter (or vice versa) boundary
> or a hyphen there
>
>>> >
>>> >> both take an enormous time. In the better case that I can subset (e.g. a=
>>> ll candidates in table 2
>>> >> share initial "AX") I get back to manageable times by adding
>>> >> and tab1.code ~ '^AX'
>>> >> into the recipe. Actual runtime with about a million entries in tab1 and=
>>> 800 entries in tab2
>>> >> is about 40 seconds.
>>>
>>> any join where result is related to some function result can be very
>>> slow, because estimation will be out and any repeated function
>>> evaluation is just expensive.
>>>
> I see the problem since obviously every the ~ operator with a non-constant
> pattern is constantly recompiling the pattern.
>
> I wonder whether it would be possible to invent a prefix-match operator that approaches
> the performance of string equality. I noted in the past (not sure whether anything
> has changed in regex matching) that a constant leading part of regex would improve
> performance, i.e. use an index scan to select possible candidates.
>
you could write a set returning function that opens cursors on both tables using
"ORDER BY code" and merges the results
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 41+ messages in thread
* Optimizing query
@ 2026-09-09 18:17 Igor Korot <ikorot01@gmail.com>
2026-09-09 22:22 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: Igor Korot @ 2026-09-09 18:17 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi, ALL,
Can below 4 queries
std::wstring query1 = L"SELECT rolname FROM pg_roles";
std::wstring query2 = L"SELECT datname FROM pg_database WHERE
datistemplate = true;";
std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
) AS name FROM pg_conversion";
std::wstring query4 = L"SELECT collname, collencoding,
collprovider collctype FROM pg_collation";
std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
be made as one big query to use a 1 DB hit?
Thank you.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2026-09-09 18:17 Optimizing query Igor Korot <ikorot01@gmail.com>
@ 2026-09-09 22:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 23:07 ` Re: Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-10 06:34 ` Re: Optimizing query Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 2 replies; 41+ messages in thread
From: Ron Johnson @ 2026-09-09 22:22 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01@gmail.com> wrote:
> Hi, ALL,
> Can below 4 queries
>
> std::wstring query1 = L"SELECT rolname FROM pg_roles";
> std::wstring query2 = L"SELECT datname FROM pg_database WHERE
> datistemplate = true;";
> std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
> ) AS name FROM pg_conversion";
> std::wstring query4 = L"SELECT collname, collencoding,
> collprovider collctype FROM pg_collation";
> std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
>
> be made as one big query to use a 1 DB hit?
>
Bereft of the C++ cruft, here are the queries:
SELECT rolname
FROM pg_roles;
SELECT datname
FROM pg_database
WHERE datistemplate = true;
SELECT pg_encoding_to_char(conforencoding) AS name
FROM pg_conversion;
SELECT collname, collencoding, collprovider, collctype
FROM pg_collation;
SELECT spcname
FROM pg_tablespace;
The sticky wicket is the four columns in the pg_collation query.
You could probably write a stored function or procedure which returns a
complex json object with the results of the four queries (or four jsonb
objects, one for each query). You'd of course have to decode the json in
your C++ program.
Without the pg_collation query, you could make a UNION ALL like:
SELECT 'pg_roles' AS table_name, rolname::text AS row_value
FROM pg_roles
UNION ALL
SELECT 'pg_database' AS table_name, datname ::text AS row_value
FROM pg_database
WHERE datistemplate = true
UNION ALL
SELECT ' pg_conversion' AS table_name
, pg_encoding_to_char(conforencoding)::text AS row_value
FROM pg_conversion
UNION ALL
SELECT pg_tablespace AS table_name, spcname ::text AS row_value
FROM pg_tablespace;
It's a lot of complication, though, for something which shouldn't be called
very often.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2026-09-09 18:17 Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-09 22:22 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
@ 2026-09-09 23:07 ` Igor Korot <ikorot01@gmail.com>
2026-09-09 22:44 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 41+ messages in thread
From: Igor Korot @ 2026-09-09 23:07 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi,
On Wed, Sep 9, 2026 at 5:23 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, ALL,
>> Can below 4 queries
>>
>> std::wstring query1 = L"SELECT rolname FROM pg_roles";
>> std::wstring query2 = L"SELECT datname FROM pg_database WHERE
>> datistemplate = true;";
>> std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
>> ) AS name FROM pg_conversion";
>> std::wstring query4 = L"SELECT collname, collencoding,
>> collprovider collctype FROM pg_collation";
>> std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
>>
>> be made as one big query to use a 1 DB hit?
>
>
> Bereft of the C++ cruft, here are the queries:
> SELECT rolname
> FROM pg_roles;
>
> SELECT datname
> FROM pg_database
> WHERE datistemplate = true;
>
> SELECT pg_encoding_to_char(conforencoding) AS name
> FROM pg_conversion;
>
> SELECT collname, collencoding, collprovider, collctype
> FROM pg_collation;
>
> SELECT spcname
> FROM pg_tablespace;
>
> The sticky wicket is the four columns in the pg_collation query.
>
> You could probably write a stored function or procedure which returns a complex json object with the results of the four queries (or four jsonb objects, one for each query). You'd of course have to decode the json in your C++ program.
>
> Without the pg_collation query, you could make a UNION ALL like:
> SELECT 'pg_roles' AS table_name, rolname::text AS row_value
> FROM pg_roles
> UNION ALL
> SELECT 'pg_database' AS table_name, datname ::text AS row_value
> FROM pg_database
> WHERE datistemplate = true
> UNION ALL
> SELECT ' pg_conversion' AS table_name
> , pg_encoding_to_char(conforencoding)::text AS row_value
> FROM pg_conversion
> UNION ALL
> SELECT pg_tablespace AS table_name, spcname ::text AS row_value
> FROM pg_tablespace;
>
> It's a lot of complication, though, for something which shouldn't be called very often.
I was hoping to have some kind of join query. ;-)
But you are right - the UNION ALL doesn't make sense for a
"1-in-a-lifetime" query.
Thank you.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2026-09-09 18:17 Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-09 22:22 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 23:07 ` Re: Optimizing query Igor Korot <ikorot01@gmail.com>
@ 2026-09-09 22:44 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 23:01 ` Re: Optimizing query Igor Korot <ikorot01@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: Ron Johnson @ 2026-09-09 22:44 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, Sep 9, 2026 at 6:33 PM Igor Korot <ikorot01@gmail.com> wrote:
> Hi,
>
> On Wed, Sep 9, 2026 at 5:23 PM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
> >
> > On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01@gmail.com> wrote:
> >>
> >> Hi, ALL,
> >> Can below 4 queries
> >>
> >> std::wstring query1 = L"SELECT rolname FROM pg_roles";
> >> std::wstring query2 = L"SELECT datname FROM pg_database WHERE
> >> datistemplate = true;";
> >> std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
> >> ) AS name FROM pg_conversion";
> >> std::wstring query4 = L"SELECT collname, collencoding,
> >> collprovider collctype FROM pg_collation";
> >> std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
> >>
> >> be made as one big query to use a 1 DB hit?
> >
> >
> > Bereft of the C++ cruft, here are the queries:
> > SELECT rolname
> > FROM pg_roles;
> >
> > SELECT datname
> > FROM pg_database
> > WHERE datistemplate = true;
> >
> > SELECT pg_encoding_to_char(conforencoding) AS name
> > FROM pg_conversion;
> >
> > SELECT collname, collencoding, collprovider, collctype
> > FROM pg_collation;
> >
> > SELECT spcname
> > FROM pg_tablespace;
> >
> > The sticky wicket is the four columns in the pg_collation query.
> >
> > You could probably write a stored function or procedure which returns a
> complex json object with the results of the four queries (or four jsonb
> objects, one for each query). You'd of course have to decode the json in
> your C++ program.
> >
> > Without the pg_collation query, you could make a UNION ALL like:
> > SELECT 'pg_roles' AS table_name, rolname::text AS row_value
> > FROM pg_roles
> > UNION ALL
> > SELECT 'pg_database' AS table_name, datname ::text AS row_value
> > FROM pg_database
> > WHERE datistemplate = true
> > UNION ALL
> > SELECT ' pg_conversion' AS table_name
> > , pg_encoding_to_char(conforencoding)::text AS row_value
> > FROM pg_conversion
> > UNION ALL
> > SELECT pg_tablespace AS table_name, spcname ::text AS row_value
> > FROM pg_tablespace;
> >
> > It's a lot of complication, though, for something which shouldn't be
> called very often.
>
> I was hoping to have some kind of join query. ;-)
> But you are right - the UNION ALL doesn't make sense for a
> "1-in-a-lifetime" query.
>
I could probably FULL OUTER JOIN them on the oid column. I don't know how
many rows are pg_collation, but if there are a lot, you're going to be
returning a whole lot of rows with null values, and application logic must
do something like "if *this* column is not null, then it's a role; if
*that* column
is not null, then it's a database; etc etc".
KISS and make four queries...
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2026-09-09 18:17 Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-09 22:22 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 23:07 ` Re: Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-09 22:44 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
@ 2026-09-09 23:01 ` Igor Korot <ikorot01@gmail.com>
0 siblings, 0 replies; 41+ messages in thread
From: Igor Korot @ 2026-09-09 23:01 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Thx.
On Wed, Sep 9, 2026 at 3:44 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Wed, Sep 9, 2026 at 6:33 PM Igor Korot <ikorot01@gmail.com> wrote:
>
>> Hi,
>>
>> On Wed, Sep 9, 2026 at 5:23 PM Ron Johnson <ronljohnsonjr@gmail.com>
>> wrote:
>> >
>> > On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01@gmail.com> wrote:
>> >>
>> >> Hi, ALL,
>> >> Can below 4 queries
>> >>
>> >> std::wstring query1 = L"SELECT rolname FROM pg_roles";
>> >> std::wstring query2 = L"SELECT datname FROM pg_database WHERE
>> >> datistemplate = true;";
>> >> std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
>> >> ) AS name FROM pg_conversion";
>> >> std::wstring query4 = L"SELECT collname, collencoding,
>> >> collprovider collctype FROM pg_collation";
>> >> std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
>> >>
>> >> be made as one big query to use a 1 DB hit?
>> >
>> >
>> > Bereft of the C++ cruft, here are the queries:
>> > SELECT rolname
>> > FROM pg_roles;
>> >
>> > SELECT datname
>> > FROM pg_database
>> > WHERE datistemplate = true;
>> >
>> > SELECT pg_encoding_to_char(conforencoding) AS name
>> > FROM pg_conversion;
>> >
>> > SELECT collname, collencoding, collprovider, collctype
>> > FROM pg_collation;
>> >
>> > SELECT spcname
>> > FROM pg_tablespace;
>> >
>> > The sticky wicket is the four columns in the pg_collation query.
>> >
>> > You could probably write a stored function or procedure which returns a
>> complex json object with the results of the four queries (or four jsonb
>> objects, one for each query). You'd of course have to decode the json in
>> your C++ program.
>> >
>> > Without the pg_collation query, you could make a UNION ALL like:
>> > SELECT 'pg_roles' AS table_name, rolname::text AS row_value
>> > FROM pg_roles
>> > UNION ALL
>> > SELECT 'pg_database' AS table_name, datname ::text AS row_value
>> > FROM pg_database
>> > WHERE datistemplate = true
>> > UNION ALL
>> > SELECT ' pg_conversion' AS table_name
>> > , pg_encoding_to_char(conforencoding)::text AS row_value
>> > FROM pg_conversion
>> > UNION ALL
>> > SELECT pg_tablespace AS table_name, spcname ::text AS row_value
>> > FROM pg_tablespace;
>> >
>> > It's a lot of complication, though, for something which shouldn't be
>> called very often.
>>
>> I was hoping to have some kind of join query. ;-)
>> But you are right - the UNION ALL doesn't make sense for a
>> "1-in-a-lifetime" query.
>>
>
> I could probably FULL OUTER JOIN them on the oid column. I don't know how
> many rows are pg_collation, but if there are a lot, you're going to be
> returning a whole lot of rows with null values, and application logic must
> do something like "if *this* column is not null, then it's a role; if
> *that* column is not null, then it's a database; etc etc".
>
> KISS and make four queries...
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Optimizing query
2026-09-09 18:17 Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-09 22:22 ` Re: Optimizing query Ron Johnson <ronljohnsonjr@gmail.com>
@ 2026-09-10 06:34 ` Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 0 replies; 41+ messages in thread
From: Peter J. Holzer @ 2026-09-10 06:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2026-09-09 18:22:52 -0400, Ron Johnson wrote:
> On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01@gmail.com> wrote:
> Can below 4 queries
>
> Â Â std::wstring query1 = L"SELECT rolname FROM pg_roles";
> Â Â std::wstring query2 = L"SELECT datname FROM pg_database WHERE
> datistemplate = true;";
> Â Â std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
> ) AS name FROM pg_conversion";
> Â Â std::wstring query4 = L"SELECT collname, collencoding,
> collprovider collctype FROM pg_collation";
> Â Â std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
>
> be made as one big query to use a 1 DB hit?
>
>
> Bereft of the C++ cruft, here are the queries:
> SELECT rolname
> FROM pg_roles;
>
> SELECT datname
> FROM pg_database
> WHERE datistemplate = true;
>
> SELECT pg_encoding_to_char(conforencoding) AS name
> FROM pg_conversion;
>
> SELECT collname, collencoding, collprovider, collctype
> FROM pg_collation;
>
> SELECT spcname
> FROM pg_tablespace;
>
> The sticky wicket is the four columns in the pg_collation query.
>
> You could probably write a stored function or procedure which returns a complex
> json object with the results of the four queries (or four jsonb objects, one
> for each query).
You don't need a stored procedure for that. Simple (although a bit
verbose) SQL is sufficient.
I'm just using two of the queries here for brevity, since the other two
have same structure as the pg_roles query.
> Without the pg_collation query, you could make a UNION ALL like:
You can still do this:
SELECT 'pg_roles' as "table", array_to_json(array_agg(rolname)) as "info"
FROM pg_roles
union all
SELECT 'pg_collation',
array_to_json(array_agg(json_build_object(
'collname', collname,
'collencoding', collencoding,
'collprovider', collprovider,
'collctype', collctype
)))
FROM pg_collation;
or you could use CTEs and a join to put them into columns of a single
row:
with pg_roles as (
SELECT array_to_json(array_agg(rolname)) as pg_roles
FROM pg_roles
),
pg_collation as (
SELECT
array_to_json(array_agg(json_build_object(
'collname', collname,
'collencoding', collencoding,
'collprovider', collprovider,
'collctype', collctype
))) as pg_collation
from pg_collation
)
select * from pg_roles, pg_collation;
Personally I would stick with the individual queries. I don't see much
value in packing all of that into a single query.
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (832B, ../../7nn77srfyydimvo7twvplrsqiazqy345rap3l5cannn2aewcsb@3zqefa2c7bav/2-signature.asc)
download
^ permalink raw reply [nested|flat] 41+ messages in thread
end of thread, other threads:[~2026-09-10 06:34 UTC | newest]
Thread overview: 41+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-24 15:57 Optimizing query Pedro Alves <pmalves@think.co.pt>
2003-01-22 10:30 optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-22 16:20 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 09:16 ` Re: optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-23 15:05 ` Re: optimizing query Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 15:26 ` Re: [PERFORM] optimizing query Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 15:52 ` Re: [PERFORM] optimizing query Chantal Ackermann <chantal.ackermann@biomax.de>
2003-01-27 00:54 ` Re: [PERFORM] optimizing query Bruce Momjian <pgman@candle.pha.pa.us>
2003-11-19 10:41 Optimizing query Uros <uros@sir-mag.com>
2003-11-19 11:44 ` Matthew Lunnon <mlunnon@rwa-net.co.uk>
2003-11-19 12:21 ` Peter Eisentraut <peter_e@gmx.net>
2003-11-19 12:23 ` Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
2003-11-19 12:25 ` Uros <uros@sir-mag.com>
2003-11-19 13:44 ` Rob Sell <lists@facnd.com>
2003-11-19 16:38 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-08-15 08:38 Optimizing query Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 09:13 ` Richard Huxton <dev@archonet.com>
2005-08-15 09:46 ` Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-15 14:54 ` Poul Møller Hansen <freebsd@pbnet.dk>
2005-08-15 10:11 ` Dennis Bjorklund <db@zigo.dhs.org>
2010-11-24 09:59 Optimizing query =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
2013-01-30 11:08 Optimizing query? wolfgang@noten5.maas-noten.de
2013-01-31 07:07 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 08:49 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 10:03 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 10:06 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 14:00 ` Re: Optimizing query? Vincent Veyron <vv.lists@wanadoo.fr>
2013-01-31 14:09 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-01-31 18:29 ` Re: Optimizing query? hamann.w@t-online.de
2013-02-01 05:50 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-02-02 09:17 ` Re: Optimizing query? hamann.w@t-online.de
2013-02-02 09:25 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-02-02 10:11 ` Re: Optimizing query? Pavel Stehule <pavel.stehule@gmail.com>
2013-02-03 05:33 ` Re: Optimizing query? Jasen Betts <jasen@xnet.co.nz>
2026-09-09 18:17 Optimizing query Igor Korot <ikorot01@gmail.com>
2026-09-09 22:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 23:07 ` Igor Korot <ikorot01@gmail.com>
2026-09-09 22:44 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 23:01 ` Igor Korot <ikorot01@gmail.com>
2026-09-10 06:34 ` Peter J. Holzer <hjp-pgsql@hjp.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox