pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery not using index
12+ messages / 9 participants
[nested] [flat]
* Query not using index
@ 2005-12-09 09:36 Kaloyan Iliev <news1@faith.digsys.bg>
2005-12-09 15:06 ` Re: Query not using index Jaime Casanova <systemguards@gmail.com>
2005-12-09 16:38 ` Re: Query not using index Kaloyan Iliev <kaloyan@mbox.digsys.bg>
2005-12-09 16:39 ` Re: Query not using index Kaloyan Iliev <news1@faith.digsys.bg>
0 siblings, 3 replies; 12+ messages in thread
From: Kaloyan Iliev @ 2005-12-09 09:36 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have a problem with a query which doeson't want to use indexes. I
tried to create different indexes but nothing help. Can anyone suggest
what index I need.
This query is executed 1.5Milion times per day and I need it to be veri
fast. I made my test on 8.0.0 beta but the production database is still
7.4.6 so i need suggestions for 7.4.6.
I will post the table with the indexes and the query plans.
iplog=# \d croute
Table "public.croute"
Column | Type | Modifiers
-----------------+--------------------------+-----------
confid | integer |
network | cidr |
comment | text |
router | text |
port | text |
valid_at | timestamp with time zone |
archived_at | timestamp with time zone |
Indexes:
"croute_netwo" btree (network) WHERE confid > 0 AND archived_at IS NULL
"croute_netwokr_valid_at" btree (network, valid_at)
"croute_network" btree (network) WHERE archived_at IS NULL
"croute_network_all" btree (network)
iplog=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk)
(1 row)
!!!!!!!!!!!!THIS IS THE QUERY!!!!!!!!!!!!!!!!!
customer=> explain analyze SELECT *
customer-> FROM croute
customer-> WHERE '193.68.0.8/32' <<=
network AND
customer-> (archived_at is NULL
OR archived_at > '17-11-2005') AND
customer-> valid_at <
'1-12-2005'::date AND
customer-> confid > 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on croute (cost=0.00..441.62 rows=413 width=102) (actual
time=14.131..37.515 rows=1 loops=1)
Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time
zone)) AND (valid_at < ('2005-12-01'::date)::timestamp with time zone)
AND (confid > 0))
Total runtime: 37.931 ms
(3 rows)
customer=> select count(*) from croute;
count
-------
10066
(1 row)
This is the result of the query:
confid | network | comment | router | port |
valid_at | archived_at |
-------+---------------+---------+------+----+-------------------------+-----------+
19971 | xx.xx.xx.xx/32 | xxxxx | ? | ? | 2005-03-11
00:00:00+02 | |
(1 row)
And last I try to stop the sequance scan but it doesn't help. I suppose
I don't have the right index.
iplog=# set enable_seqscan = off;
SET
iplog=# explain analyze SELECT *
iplog-# FROM croute
iplog-# WHERE '193.68.0.8/32' <<=
network AND
iplog-# (archived_at is NULL OR
archived_at > '17-11-2005') AND
iplog-# valid_at <
'1-12-2005'::date AND
iplog-# confid > 0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on croute (cost=100000000.00..100000780.64 rows=1030
width=103) (actual time=29.593..29.819 rows=1 loops=1)
Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time
zone)) AND (valid_at < '2005-12-01'::date) AND (confid > 0))
Total runtime: 29.931 ms
(3 rows)
I try creating one last index on all fields but it doesn't help.
iplog=# CREATE INDEX croute_all on
croute(network,archived_at,valid_at,confid);
CREATE INDEX
iplog=# explain analyze SELECT *
iplog-# FROM croute
iplog-# WHERE '193.68.0.8/32' <<=
network AND
iplog-# (archived_at is NULL OR
archived_at > '17-11-2005') AND
iplog-# valid_at <
'1-12-2005'::date AND
iplog-# confid > 0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on croute (cost=100000000.00..100000780.64 rows=1030
width=103) (actual time=29.626..29.879 rows=1 loops=1)
Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time
zone)) AND (valid_at < '2005-12-01'::date) AND (confid > 0))
Total runtime: 30.060 ms
(3 rows)
Thanks in advance to all.
Kaloyan Iliev
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query not using index
2005-12-09 09:36 Query not using index Kaloyan Iliev <news1@faith.digsys.bg>
@ 2005-12-09 15:06 ` Jaime Casanova <systemguards@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Jaime Casanova @ 2005-12-09 15:06 UTC (permalink / raw)
To: Kaloyan Iliev <news1@faith.digsys.bg>; +Cc: pgsql-performance
On 12/9/05, Kaloyan Iliev <news1@faith.digsys.bg> wrote:
> Hi all,
>
> I have a problem with a query which doeson't want to use indexes. I
> tried to create different indexes but nothing help. Can anyone suggest
> what index I need.
> This query is executed 1.5Milion times per day and I need it to be veri
> fast. I made my test on 8.0.0 beta but the production database is still
> 7.4.6 so i need suggestions for 7.4.6.
> I will post the table with the indexes and the query plans.
> iplog=# \d croute
> Table "public.croute"
> Column | Type | Modifiers
> -----------------+--------------------------+-----------
> confid | integer |
> network | cidr |
> comment | text |
> router | text |
> port | text |
> valid_at | timestamp with time zone |
> archived_at | timestamp with time zone |
> Indexes:
> "croute_netwo" btree (network) WHERE confid > 0 AND archived_at IS NULL
> "croute_netwokr_valid_at" btree (network, valid_at)
> "croute_network" btree (network) WHERE archived_at IS NULL
> "croute_network_all" btree (network)
>
>
> iplog=# select version();
> version
> --------------------------------------------------------------------------------------------------------------
> PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk)
> (1 row)
>
> !!!!!!!!!!!!THIS IS THE QUERY!!!!!!!!!!!!!!!!!
> customer=> explain analyze SELECT *
> customer-> FROM croute
> customer-> WHERE '193.68.0.8/32' <<=
> network AND
> customer-> (archived_at is NULL
> OR archived_at > '17-11-2005') AND
> customer-> valid_at <
> '1-12-2005'::date AND
> customer-> confid > 0;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on croute (cost=0.00..441.62 rows=413 width=102) (actual
> time=14.131..37.515 rows=1 loops=1)
> Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
> NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time
> zone)) AND (valid_at < ('2005-12-01'::date)::timestamp with time zone)
> AND (confid > 0))
> Total runtime: 37.931 ms
> (3 rows)
>
> customer=> select count(*) from croute;
> count
> -------
> 10066
> (1 row)
> This is the result of the query:
> confid | network | comment | router | port |
> valid_at | archived_at |
> -------+---------------+---------+------+----+-------------------------+-----------+
> 19971 | xx.xx.xx.xx/32 | xxxxx | ? | ? | 2005-03-11
> 00:00:00+02 | |
> (1 row)
> And last I try to stop the sequance scan but it doesn't help. I suppose
> I don't have the right index.
> iplog=# set enable_seqscan = off;
> SET
> iplog=# explain analyze SELECT *
> iplog-# FROM croute
> iplog-# WHERE '193.68.0.8/32' <<=
> network AND
> iplog-# (archived_at is NULL OR
> archived_at > '17-11-2005') AND
> iplog-# valid_at <
> '1-12-2005'::date AND
> iplog-# confid > 0;
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on croute (cost=100000000.00..100000780.64 rows=1030
> width=103) (actual time=29.593..29.819 rows=1 loops=1)
> Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
> NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time
> zone)) AND (valid_at < '2005-12-01'::date) AND (confid > 0))
> Total runtime: 29.931 ms
> (3 rows)
>
> I try creating one last index on all fields but it doesn't help.
> iplog=# CREATE INDEX croute_all on
> croute(network,archived_at,valid_at,confid);
> CREATE INDEX
> iplog=# explain analyze SELECT *
> iplog-# FROM croute
> iplog-# WHERE '193.68.0.8/32' <<=
> network AND
> iplog-# (archived_at is NULL OR
> archived_at > '17-11-2005') AND
> iplog-# valid_at <
> '1-12-2005'::date AND
> iplog-# confid > 0;
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on croute (cost=100000000.00..100000780.64 rows=1030
> width=103) (actual time=29.626..29.879 rows=1 loops=1)
> Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
> NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time
> zone)) AND (valid_at < '2005-12-01'::date) AND (confid > 0))
> Total runtime: 30.060 ms
> (3 rows)
>
>
> Thanks in advance to all.
>
> Kaloyan Iliev
>
>
In oracle you can use this instead...
SELECT * FROM croute
WHERE '193.68.0.8/32' <<= network
AND archived_at is NULL
AND valid_at < '1-12-2005'::date
AND confid > 0;
UNION
SELECT * FROM croute
WHERE '193.68.0.8/32' <<= network
AND archived_at > '17-11-2005'::date
AND valid_at < '1-12-2005'::date
AND confid > 0;
although i think that your query can make use of bitmap index in 8.1
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query not using index
2005-12-09 09:36 Query not using index Kaloyan Iliev <news1@faith.digsys.bg>
@ 2005-12-09 16:38 ` Kaloyan Iliev <kaloyan@mbox.digsys.bg>
2 siblings, 0 replies; 12+ messages in thread
From: Kaloyan Iliev @ 2005-12-09 16:38 UTC (permalink / raw)
To: pgsql-performance
Hi all,
Thanks for the reply. I made some more test and find out that the
problem is with the <<= operator for the network type. Can I create
index which to work with <<=. Because if I use = the index is used. But
not for <<=.
iplog=# explain analyze SELECT *
iplog-# FROM croute
iplog-# WHERE '193.68.0.10/32' <<=
network;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on croute (cost=0.00..707.27 rows=4891 width=103) (actual
time=10.313..29.621 rows=2 loops=1)
Filter: ('193.68.0.10/32'::cidr <<= network)
Total runtime: 29.729 ms
(3 rows)
iplog=# explain analyze SELECT *
iplog-# FROM croute
iplog-# WHERE '193.68.0.10/32' = network;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using croute_network_all on croute (cost=0.00..17.99 rows=4
width=103) (actual time=0.053..0.059 rows=1 loops=1)
Index Cond: ('193.68.0.10/32'::cidr = network)
Total runtime: 0.167 ms
(3 rows)
Waiting for replies.
Thanks to all in advance.
Kaloyan Iliev
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query not using index
2005-12-09 09:36 Query not using index Kaloyan Iliev <news1@faith.digsys.bg>
@ 2005-12-09 16:39 ` Kaloyan Iliev <news1@faith.digsys.bg>
2 siblings, 0 replies; 12+ messages in thread
From: Kaloyan Iliev @ 2005-12-09 16:39 UTC (permalink / raw)
To: pgsql-performance
Hi all,
Thanks for the reply. I made some more test and find out that the
problem is with the <<= operator for the network type. Can I create
index which to work with <<=. Because if I use = the index is used. But
not for <<=.
iplog=# explain analyze SELECT *
iplog-# FROM croute
iplog-# WHERE '193.68.0.10/32' <<=
network;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on croute (cost=0.00..707.27 rows=4891 width=103) (actual
time=10.313..29.621 rows=2 loops=1)
Filter: ('193.68.0.10/32'::cidr <<= network)
Total runtime: 29.729 ms
(3 rows)
iplog=# explain analyze SELECT *
iplog-# FROM croute
iplog-# WHERE '193.68.0.10/32' = network;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using croute_network_all on croute (cost=0.00..17.99 rows=4
width=103) (actual time=0.053..0.059 rows=1 loops=1)
Index Cond: ('193.68.0.10/32'::cidr = network)
Total runtime: 0.167 ms
(3 rows)
Waiting for replies.
Thanks to all in advance.
Kaloyan Iliev
^ permalink raw reply [nested|flat] 12+ messages in thread
* query not using index
@ 2013-12-19 13:06 Johann Spies <johann.spies@gmail.com>
2013-12-19 14:48 ` Re: query not using index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Johann Spies @ 2013-12-19 13:06 UTC (permalink / raw)
To: pgsql-performance
I would appreciate some help optimising the following query:
with
subject_journals as(
select A.sq
from isi.rissue A,
isi.rsc_joern_link C
WHERE
C.sc_id in
('d0963875-e438-4923-b3fa-f462e8975221',
'04e14284-09c8-421a-b1ad-c8238051601a',
'04e2189f-cd2a-44f0-b98d-52f6bb5dcd78',
'f5521c65-ec49-408a-9a42-8a69d47703cd',
'2e47ae2f-2c4d-433e-8bdf-9983eeeafc42',
'5d3639b1-04c2-4d94-a99a-5323277fd2b7')
AND
C.rj_id = A.uuid),
subject_articles as (
SELECT B.article_id as art_id
FROM
isi.isi_l1_publication B,
subject_journals A,
isi.ritem C
WHERE
A.sq = B.journal_id
AND
B.publication_year <= '2012'
AND
B.publication_year >= '2000'
AND
C.ut = B.article_id
AND
C.dt in ('@ Article','Review')
),
country_articles as (
SELECT A.art_id
FROM isi.art_country_link A
WHERE
A.countrycode = 'ZA')
select art_id from subject_articles
INTERSECT
select art_id from country_articles
Analyze explains shows that it is not using the indexes on both
isi.isi_l1_publication and isi.ritem (both tables with more than 43 million
records).:
"HashSetOp Intersect (cost=10778065.50..11227099.44 rows=200 width=48)
(actual time=263120.868..263279.467 rows=4000 loops=1)"
" Output: "*SELECT* 1".art_id, (0)"
" Buffers: shared hit=627401 read=4347235, temp read=234498 written=234492"
" CTE subject_journals"
" -> Hash Join (cost=12846.55..17503.27 rows=28818 width=8) (actual
time=99.762..142.439 rows=30291 loops=1)"
" Output: a.sq"
" Hash Cond: ((c.rj_id)::text = (a.uuid)::text)"
" Buffers: shared hit=12232"
" -> Bitmap Heap Scan on isi.rsc_joern_link c
(cost=1020.92..5029.23 rows=28818 width=37) (actual time=4.238..15.806
rows=30291 loops=1)"
" Output: c.id, c.rj_id, c.sc_id"
" Recheck Cond: ((c.sc_id)::text = ANY
('{d0963875-e438-4923-b3fa-f462e8975221,04e14284-09c8-421a-b1ad-c8238051601a,04e2189f-cd2a-44f0-b98d-52f6bb5dcd78,f5521c65-ec49-408a-9a42-8a69d47703cd,2e47ae2f-2c4d-433e-8bdf-9983eeeafc42,5d3639b1-04c2-4
(...)"
" Buffers: shared hit=3516"
" -> Bitmap Index Scan on rsc_joern_link_sc_id_idx
(cost=0.00..1013.72 rows=28818 width=0) (actual time=3.722..3.722
rows=30291 loops=1)"
" Index Cond: ((c.sc_id)::text = ANY
('{d0963875-e438-4923-b3fa-f462e8975221,04e14284-09c8-421a-b1ad-c8238051601a,04e2189f-cd2a-44f0-b98d-52f6bb5dcd78,f5521c65-ec49-408a-9a42-8a69d47703cd,2e47ae2f-2c4d-433e-8bdf-9983eeeafc42,5d3639b1-04
(...)"
" Buffers: shared hit=237"
" -> Hash (cost=10098.06..10098.06 rows=138206 width=45) (actual
time=95.495..95.495 rows=138206 loops=1)"
" Output: a.sq, a.uuid"
" Buckets: 16384 Batches: 1 Memory Usage: 10393kB"
" Buffers: shared hit=8716"
" -> Seq Scan on isi.rissue a (cost=0.00..10098.06
rows=138206 width=45) (actual time=0.005..58.225 rows=138206 loops=1)"
" Output: a.sq, a.uuid"
" Buffers: shared hit=8716"
" CTE subject_articles"
" -> Merge Join (cost=9660996.21..9896868.27 rows=13571895 width=16)
(actual time=229449.020..259557.073 rows=2513896 loops=1)"
" Output: b.article_id"
" Merge Cond: ((a.sq)::text = (b.journal_id)::text)"
" Buffers: shared hit=519891 read=4347235, temp read=234498
written=234492"
" -> Sort (cost=2711.01..2783.05 rows=28818 width=32) (actual
time=224.901..230.615 rows=30288 loops=1)"
" Output: a.sq"
" Sort Key: a.sq"
" Sort Method: quicksort Memory: 2188kB"
" Buffers: shared hit=12232"
" -> CTE Scan on subject_journals a (cost=0.00..576.36
rows=28818 width=32) (actual time=99.764..152.459 rows=30291 loops=1)"
" Output: a.sq"
" Buffers: shared hit=12232"
" -> Materialize (cost=9658285.21..9722584.29 rows=12859816
width=24) (actual time=229223.851..253191.308 rows=14664245 loops=1)"
" Output: b.article_id, b.journal_id"
" Buffers: shared hit=507659 read=4347235, temp read=234498
written=234492"
" -> Sort (cost=9658285.21..9690434.75 rows=12859816
width=24) (actual time=229223.846..251142.167 rows=14072645 loops=1)"
" Output: b.article_id, b.journal_id"
" Sort Key: b.journal_id"
" Sort Method: external merge Disk: 467704kB"
" Buffers: shared hit=507659 read=4347235, temp
read=234498 written=234492"
" -> Hash Join (cost=1474828.02..7876046.06
rows=12859816 width=24) (actual time=27181.734..91781.942 rows=14072645
loops=1)"
" Output: b.article_id, b.journal_id"
" Hash Cond: ((c.ut)::text =
(b.article_id)::text)"
" Buffers: shared hit=507659 read=4347235, temp
read=176031 written=176025"
" -> Seq Scan on isi.ritem c
(cost=0.00..5071936.72 rows=29104515 width=16) (actual
time=0.012..25529.577 rows=29182778 loops=1)"
" Output: c.ut"
" Filter: (((c.dt)::text = '@
Article'::text) OR ((c.dt)::text = 'Review'::text))"
" Buffers: shared hit=52128 read=4347235"
" -> Hash (cost=1111096.04..1111096.04
rows=19811758 width=24) (actual time=27176.450..27176.450 rows=19820997
loops=1)"
" Output: b.article_id, b.journal_id"
" Buckets: 1048576 Batches: 4 Memory
Usage: 271177kB"
" Buffers: shared hit=455531, temp
written=79848"
" -> Seq Scan on isi.isi_l1_publication
b (cost=0.00..1111096.04 rows=19811758 width=24) (actual
time=152.219..21215.614 rows=19820997 loops=1)"
" Output: b.article_id, b.journal_id"
" Filter:
(((b.publication_year)::text < '2012'::text) AND
((b.publication_year)::text > '1999'::text))"
" Buffers: shared hit=455531"
" CTE country_articles"
" -> Bitmap Heap Scan on isi.art_country_link a
(cost=6427.92..863693.95 rows=244534 width=16) (actual time=65.053..256.632
rows=205195 loops=1)"
" Output: a.art_id"
" Recheck Cond: ((a.countrycode)::text = 'ZA'::text)"
" Buffers: shared hit=107510"
" -> Bitmap Index Scan on art_country_link_countrycode_idx
(cost=0.00..6366.79 rows=244534 width=0) (actual time=36.481..36.481
rows=205195 loops=1)"
" Index Cond: ((a.countrycode)::text = 'ZA'::text)"
" Buffers: shared hit=603"
" -> Append (cost=0.00..414492.87 rows=13816429 width=48) (actual
time=229449.025..261565.050 rows=2719091 loops=1)"
" Buffers: shared hit=627401 read=4347235, temp read=234498
written=234492"
" -> Subquery Scan on "*SELECT* 1" (cost=0.00..407156.85
rows=13571895 width=48) (actual time=229449.025..260892.314 rows=2513896
loops=1)"
" Output: "*SELECT* 1".art_id, 0"
" Buffers: shared hit=519891 read=4347235, temp read=234498
written=234492"
" -> CTE Scan on subject_articles (cost=0.00..271437.90
rows=13571895 width=48) (actual time=229449.024..260423.294 rows=2513896
loops=1)"
" Output: subject_articles.art_id"
" Buffers: shared hit=519891 read=4347235, temp
read=234498 written=234492"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..7336.02 rows=244534
width=48) (actual time=65.059..353.671 rows=205195 loops=1)"
" Output: "*SELECT* 2".art_id, 1"
" Buffers: shared hit=107510"
" -> CTE Scan on country_articles (cost=0.00..4890.68
rows=244534 width=48) (actual time=65.057..320.444 rows=205195 loops=1)"
" Output: country_articles.art_id"
" Buffers: shared hit=107510"
"Total runtime: 263466.781 ms"
The index for those fields:
CREATE INDEX isi_l1_publication_publication_year_idx
ON isi.isi_l1_publication
USING btree
(publication_year COLLATE pg_catalog."default");
CREATE INDEX ritem_dt_idx
ON isi.ritem
USING btree
(dt COLLATE pg_catalog."default");
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query not using index
2013-12-19 13:06 query not using index Johann Spies <johann.spies@gmail.com>
@ 2013-12-19 14:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-12-23 08:58 ` Re: query not using index Johann Spies <johann.spies@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2013-12-19 14:48 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
Johann Spies <johann.spies@gmail.com> writes:
> I would appreciate some help optimising the following query:
It's a mistake to imagine that indexes are going to help much with
a join of this size. Hash or merge join is going to be a lot better
than nestloop. What you need to do is make sure those will perform
as well as possible, and to that end, it'd likely help to raise
work_mem. I'm not sure if you can sanely put it high enough to
make the query operate totally in memory --- it looks like you'd
need work_mem of 500MB or more to prevent any of the sorts or
hashes from spilling to disk, and keep in mind that this query
is going to use several times work_mem because there are multiple
sorts/hashes going on. But if you can transiently dedicate a lot
of RAM to this query, that should help some. I'd suggest increasing
work_mem via a SET command in the particular session running this
query --- you don't want such a high value to be the global default.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query not using index
2013-12-19 13:06 query not using index Johann Spies <johann.spies@gmail.com>
2013-12-19 14:48 ` Re: query not using index Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-12-23 08:58 ` Johann Spies <johann.spies@gmail.com>
2013-12-28 02:31 ` Re: query not using index Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 12+ messages in thread
From: Johann Spies @ 2013-12-23 08:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 19 December 2013 16:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Johann Spies <johann.spies@gmail.com> writes:
> > I would appreciate some help optimising the following query:
>
> It's a mistake to imagine that indexes are going to help much with
> a join of this size. Hash or merge join is going to be a lot better
> than nestloop. What you need to do is make sure those will perform
> as well as possible, and to that end, it'd likely help to raise
> work_mem. I'm not sure if you can sanely put it high enough to
> make the query operate totally in memory --- it looks like you'd
> need work_mem of 500MB or more to prevent any of the sorts or
> hashes from spilling to disk, and keep in mind that this query
> is going to use several times work_mem because there are multiple
> sorts/hashes going on. But if you can transiently dedicate a lot
> of RAM to this query, that should help some. I'd suggest increasing
> work_mem via a SET command in the particular session running this
> query --- you don't want such a high value to be the global default.
>
Thanks Tom. Raising work_mem from 384MB to 512MB made a significant
difference.
You said "hash or merge join id going to be a lot better than nestloop".
Is that purely in the hands of the query planner or what can I do to get
the planner to use that options apart from raising the work_mem?
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query not using index
2013-12-19 13:06 query not using index Johann Spies <johann.spies@gmail.com>
2013-12-19 14:48 ` Re: query not using index Tom Lane <tgl@sss.pgh.pa.us>
2013-12-23 08:58 ` Re: query not using index Johann Spies <johann.spies@gmail.com>
@ 2013-12-28 02:31 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 12+ messages in thread
From: Mark Kirkwood @ 2013-12-28 02:31 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 23/12/13 21:58, Johann Spies wrote:
>
>
>
> On 19 December 2013 16:48, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Johann Spies <johann.spies@gmail.com
> <mailto:johann.spies@gmail.com>> writes:
> > I would appreciate some help optimising the following query:
>
> It's a mistake to imagine that indexes are going to help much with
> a join of this size. Hash or merge join is going to be a lot better
> than nestloop. What you need to do is make sure those will perform
> as well as possible, and to that end, it'd likely help to raise
> work_mem. I'm not sure if you can sanely put it high enough to
> make the query operate totally in memory --- it looks like you'd
> need work_mem of 500MB or more to prevent any of the sorts or
> hashes from spilling to disk, and keep in mind that this query
> is going to use several times work_mem because there are multiple
> sorts/hashes going on. But if you can transiently dedicate a lot
> of RAM to this query, that should help some. I'd suggest increasing
> work_mem via a SET command in the particular session running this
> query --- you don't want such a high value to be the global default.
>
>
> Thanks Tom. Raising work_mem from 384MB to 512MB made a significant
> difference.
>
> You said "hash or merge join id going to be a lot better than
> nestloop". Is that purely in the hands of the query planner or what can
> I do to get the planner to use that options apart from raising the work_mem?
>
>
You can disable the hash and merge join options by doing:
SET enable_hashjoin=off;
SET enable_mergejoin=off;
before running the query again. Timing it (or EXPLAIN ANALYZE) should
demonstrate if that planner made the right call by choosing hash or
merge in the first place.
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Query not using Index
@ 2016-03-26 13:14 Wei Shan <weishan.ang@gmail.com>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 12+ messages in thread
From: Wei Shan @ 2016-03-26 13:14 UTC (permalink / raw)
To: pgsql-performance
Hi all,
Please provide some advise on the following query not using the index:
pgsql version: 9.2.4
OS version: RedHat 6.5
Ram: 64 GB
rows in testdb: 180 million
shared_buffers: 16GB
effective_cache_size: 32GB
work_mem='32MB'
I have executed the query below after I vaccum analyze the table.
I have 2 questions:
1. Why does the optimizer chose not to use the index when it will run
faster?
2. How do I ensure the optimizer will use the index without setting
enable_seqscan='off'
*Table structure.*
testdb=# \d testtable
Table "public.testtable"
Column | Type | Modifiers
-------------------+---------+-----------
pk | text | not null
additionaldetails | text |
authtoken | text | not null
customid | text |
eventstatus | text | not null
eventtype | text | not null
module | text | not null
nodeid | text | not null
rowprotection | text |
rowversion | integer | not null
searchdetail1 | text |
searchdetail2 | text |
sequencenumber | bigint | not null
service | text | not null
timestamp | bigint | not null
Indexes:
"testtable_pkey" PRIMARY KEY, btree (pk)
"testtable_nodeid_eleanor1_idx" btree (nodeid) WHERE nodeid =
'eleanor1'::text, tablespace "tablespace_index"
"testtable_nodeid_eleanor2_idx" btree (nodeid) WHERE nodeid =
'eleanor2'::text, tablespace "tablespace_index"
"testtable_nodeid_eleanor3_idx" btree (nodeid) WHERE nodeid =
'eleanor3'::text, tablespace "tablespace_index"
*Explain Plan with enable_seqscan='on'*
testdb=# explain analyze select max ( auditrecor0_.sequenceNumber ) AS
col_0_0_ From testdb auditrecor0_ where auditrecor0_.nodeid = 'eleanor1';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------
-------------------------
Aggregate (cost=18291486.05..18291486.06 rows=1 width=8) (actual
time=484907.446..484907.446 rows=1 loops=1)
-> Seq Scan on testdb auditrecor0_ (cost=0.00..18147465.00
rows=57608421 width=8) (actual time=0.166..473959.12
6 rows=57801797 loops=1)
Filter: (nodeid = 'eleanor1'::text)
Rows Removed by Filter: 126233820
Total runtime: 484913.013 ms
(5 rows)
*Explain Plan with enable_seqscan='off'*
testdb=# explain analyze select max ( auditrecor0_.sequenceNumber ) AS
col_0_0_ From testdb auditrecor0_ where auditrecor0_.nodeid = 'eleanor3';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------
Aggregate (cost=19226040.50..19226040.51 rows=1 width=8) (actual
time=388293.245..388293.245 rows=1 loops=1)
-> Bitmap Heap Scan on testdb auditrecor0_
(cost=2291521.32..19046381.97 rows=71863412 width=8) (actual
time=15626.372..375378.362 rows=71
412687 loops=1)
Recheck Cond: (nodeid = 'eleanor3'::text)
Rows Removed by Index Recheck: 900820
-> Bitmap Index Scan on testdb_nodeid_eleanor3_idx
(cost=0.00..2273555.47 rows=71863412 width=0) (actual
time=15503.465..15503.465 r
ows=71412687 loops=1)
Index Cond: (nodeid = 'eleanor3'::text)
Total runtime: 388294.378 ms
(7 rows)
Thanks!
--
Regards,
Ang Wei Shan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query not using Index
2016-03-26 13:14 Query not using Index Wei Shan <weishan.ang@gmail.com>
@ 2016-03-26 14:13 ` Andreas Kretschmer <akretschmer@spamfence.net>
2016-03-27 16:12 ` Re: Query not using Index Wei Shan <weishan.ang@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Kretschmer @ 2016-03-26 14:13 UTC (permalink / raw)
To: pgsql-performance
Wei Shan <weishan.ang@gmail.com> wrote:
> Hi all,
>
> Please provide some advise on the following query not using the index:
> I have 2 questions:
>
> 1. Why does the optimizer chose not to use the index when it will run faster?
because of the estimated costs.:
Seq Scan on testdb auditrecor0_ (cost=0.00..18147465.00
Bitmap Heap Scan on testdb auditrecor0_ (cost=2291521.32..19046381.97
The estimated costs for the index-scan are higher.
> 2. How do I ensure the optimizer will use the index without setting
> enable_seqscan='off'
You have a dedicated tablespace for indexes, is this a SSD? You can try
to reduce the random_page_cost, from default 4 to maybe 2.(depends on
hardware) This would reduce the estimated costs for the Index-scan and
prefer the index-scan.
Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query not using Index
2016-03-26 13:14 Query not using Index Wei Shan <weishan.ang@gmail.com>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <akretschmer@spamfence.net>
@ 2016-03-27 16:12 ` Wei Shan <weishan.ang@gmail.com>
2016-03-27 19:20 ` Re: Query not using Index Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wei Shan @ 2016-03-27 16:12 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
Hi Andreas,
The tablespace is not on SSD although I intend to do it within the next
week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
help.
On 26 March 2016 at 22:13, Andreas Kretschmer <akretschmer@spamfence.net>
wrote:
> Wei Shan <weishan.ang@gmail.com> wrote:
>
> > Hi all,
> >
> > Please provide some advise on the following query not using the index:
> > I have 2 questions:
> >
> > 1. Why does the optimizer chose not to use the index when it will run
> faster?
>
> because of the estimated costs.:
>
> Seq Scan on testdb auditrecor0_ (cost=0.00..18147465.00
> Bitmap Heap Scan on testdb auditrecor0_ (cost=2291521.32..19046381.97
>
> The estimated costs for the index-scan are higher.
>
>
> > 2. How do I ensure the optimizer will use the index without setting
> > enable_seqscan='off'
>
> You have a dedicated tablespace for indexes, is this a SSD? You can try
> to reduce the random_page_cost, from default 4 to maybe 2.(depends on
> hardware) This would reduce the estimated costs for the Index-scan and
> prefer the index-scan.
>
>
>
> Regards, Andreas Kretschmer
> --
> Andreas Kretschmer
> http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Regards,
Ang Wei Shan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Query not using Index
2016-03-26 13:14 Query not using Index Wei Shan <weishan.ang@gmail.com>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <akretschmer@spamfence.net>
2016-03-27 16:12 ` Re: Query not using Index Wei Shan <weishan.ang@gmail.com>
@ 2016-03-27 19:20 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Jeff Janes @ 2016-03-27 19:20 UTC (permalink / raw)
To: Wei Shan <weishan.ang@gmail.com>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-performance
On Sun, Mar 27, 2016 at 9:12 AM, Wei Shan <weishan.ang@gmail.com> wrote:
> Hi Andreas,
>
> The tablespace is not on SSD although I intend to do it within the next
> week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
> help.
Setting random_page_cost to less than seq_page_cost is nonsensical.
You could try to increase cpu_tuple_cost to 0.015 or 0.02
Cheers,
Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2016-03-27 19:20 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-09 09:36 Query not using index Kaloyan Iliev <news1@faith.digsys.bg>
2005-12-09 15:06 ` Jaime Casanova <systemguards@gmail.com>
2005-12-09 16:38 ` Kaloyan Iliev <kaloyan@mbox.digsys.bg>
2005-12-09 16:39 ` Kaloyan Iliev <news1@faith.digsys.bg>
2013-12-19 13:06 query not using index Johann Spies <johann.spies@gmail.com>
2013-12-19 14:48 ` Re: query not using index Tom Lane <tgl@sss.pgh.pa.us>
2013-12-23 08:58 ` Re: query not using index Johann Spies <johann.spies@gmail.com>
2013-12-28 02:31 ` Re: query not using index Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2016-03-26 13:14 Query not using Index Wei Shan <weishan.ang@gmail.com>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <akretschmer@spamfence.net>
2016-03-27 16:12 ` Re: Query not using Index Wei Shan <weishan.ang@gmail.com>
2016-03-27 19:20 ` Re: Query not using Index Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox