Обсуждение: very slow query
Hi,
I am having PostgreSQL 8.2.4 on Suse 10.3
Server is not using the index insted it chooses to take seq scan path. table is having @ 120 million rows
here is the output from planner:
Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
-> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
Filter: (mobile = 919820920858::bigint)
-> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1 width=8)
Index Cond: ("outer".deliveryid = delivery.deliveryid)
Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone) AND ((keyword)::text = 'CRI'::text))
column deliveryid has an index over it in sms_new table.
can anybody please guide to force the usage of index
Thanks in advance
With Regards
Ashish
Get the freedom to save as many mails as you wish. Click here to know how.
I am having PostgreSQL 8.2.4 on Suse 10.3
Server is not using the index insted it chooses to take seq scan path. table is having @ 120 million rows
here is the output from planner:
Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
-> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
Filter: (mobile = 919820920858::bigint)
-> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1 width=8)
Index Cond: ("outer".deliveryid = delivery.deliveryid)
Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone) AND ((keyword)::text = 'CRI'::text))
column deliveryid has an index over it in sms_new table.
can anybody please guide to force the usage of index
Thanks in advance
With Regards
Ashish
Get the freedom to save as many mails as you wish. Click here to know how.
am Wed, dem 12.12.2007, um 11:44:58 +0000 mailte Ashish Karalkar folgendes:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
>
> Server is not using the index insted it chooses to take seq scan path. table is
> having @ 120 million rows
>
> here is the output from planner:
> Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> Filter: (mobile = 919820920858::bigint)
> -> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1
> width=8)
> Index Cond: ("outer".deliveryid = delivery.deliveryid)
> Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time
> zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone)
> AND ((keyword)::text = 'CRI'::text))
>
> column deliveryid has an index over it in sms_new table.
>
> can anybody please guide to force the usage of index
It's using an index on "deliveryid":
Index Scan using deliveryid_pkey
Do you have an index on "mobile"? Can you show us the table definition
and the output from EXPLAIN ANALYSE?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Wed, dem 12.12.2007, um 12:25:20 +0000 mailte Ashish Karalkar folgendes: > > here is the output from planner: > > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106) > > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106) > > Filter: (mobile = 919820920858::bigint) > > Do you have an index on "mobile"? Can you show us the table definition > and the output from EXPLAIN ANALYSE? > > no index on mobile Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint) (cost=0.00..5027902.00 Can you see the problem? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Wed, dem 12.12.2007, um 12:44:09 +0000 mailte Ashish Karalkar folgendes: > > Do you have an index on "mobile"? Can you show us the table definition > > and the output from EXPLAIN ANALYSE? > > > > no index on mobile > > Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint) > (cost=0.00..5027902.00 > > > Can you see the problem? > > So i will have to create index on mobile is taht so? Try it. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
am Wed, dem 12.12.2007, um 12:44:09 +0000 mailte Ashish Karalkar folgendes:
> > Do you have an index on "mobile"? Can you show us the table definition
> > and the output from EXPLAIN ANALYSE?
> >
> > no index on mobile
>
> Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
> (cost=0.00..5027902.00
>
>
> Can you see the problem?
>
> So i will have to create index on mobile is taht so?
Try it.
Thanks for your answer actually that was the part of full query here is the actuall plan
HashAggregate (cost=5895532.37..5895534.35 rows=158 width=32)
-> Hash Join (cost=215823.74..5895449.38 rows=5533 width=32)
Hash Cond: ("outer".deliveryid = "inner".deliveryid)
-> Seq Scan on sms_new (cost=0.00..5038183.09 rows=128277444 width=8)
Filter: ((otid)::text !~~ 'ERROR%'::text)
-> Hash (cost=215823.35..215823.35 rows=158 width=32)
-> Bitmap Heap Scan on delivery (cost=2178.24..215823.35 rows=158 width=32)
Recheck Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
Filter: ((taskid = 1024) AND (((remoteip)::text = '192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = '192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = '202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR ((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR ((remoteip)::text = '202.162.231.7'::text)))
-> Bitmap Index Scan on createddate_idx (cost=0.00..2178.24 rows=195039 width=0)
Index Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
any suggestion on this
With Regards
Ashish...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Get the freedom to save as many mails as you wish. Click here to know how.
Ashish Karalkar wrote:
> Thanks for your answer actually that was the part of full query here is the actuall plan
I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".
On this plan it looks like you need an index on otid. Or maybe not.
It's hard to tell with only an EXPLAIN.
> HashAggregate (cost=5895532.37..5895534.35 rows=158 width=32)
> -> Hash Join (cost=215823.74..5895449.38 rows=5533 width=32)
> Hash Cond: ("outer".deliveryid = "inner".deliveryid)
> -> Seq Scan on sms_new (cost=0.00..5038183.09 rows=128277444 width=8)
> Filter: ((otid)::text !~~ 'ERROR%'::text)
> -> Hash (cost=215823.35..215823.35 rows=158 width=32)
> -> Bitmap Heap Scan on delivery (cost=2178.24..215823.35 rows=158 width=32)
> Recheck Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND
(createddate<= '2007-12-11 23:59:59'::timestamp without time zone))
> Filter: ((taskid = 1024) AND (((remoteip)::text = '192.168.3.26'::text) OR ((remoteip)::text =
'202.162.231.230'::text)OR ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = '192.168.4.3'::text) OR
((remoteip)::text= '192.168.3.3'::text) OR ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text =
'202.162.231.5'::text)OR ((remoteip)::text = '202.162.231.1'::text) OR ((remoteip)::text = '192.168.4.6'::text) OR
((remoteip)::text= '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR ((remoteip)::text =
'202.162.231.7'::text)))
> -> Bitmap Index Scan on createddate_idx (cost=0.00..2178.24 rows=195039 width=0)
> Index Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND
(createddate<= '2007-12-11 23:59:59'::timestamp without time zone))
--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Ashish Karalkar wrote:
> Thanks for your answer actually that was the part of full query here is the actuall plan
I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".
Actually all issue is with sms_new table which is having 120 M rows.
an planer is ignoring to use index on delivery id.
EXPLAIN ANALYSE too take long time to show output :(
On this plan it looks like you need an index on otid. Or maybe not.
It's hard to tell with only an EXPLAIN.
> HashAggregate (cost=5895532.37..5895534.35 rows=158 width=32)
> -> Hash Join (cost=215823.74..5895449.38 rows=5533 width=32)
> Hash Cond: ("outer".deliveryid = "inner".deliveryid)
> -> Seq Scan on sms_new (cost=0.00..5038183.09 rows=128277444 width=8)
> Filter: ((otid)::text !~~ 'ERROR%'::text)
> -> Hash (cost=215823.35..215823.35 rows=158 width=32)
> -> Bitmap Heap Scan on delivery (cost=2178.24..215823.35 rows=158 width=32)
> Recheck Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
> Filter: ((taskid = 1024) AND (((remoteip)::text = '192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = '192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = '202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR ((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR ((remoteip)::text = '202.162.231.7'::text)))
> -> Bitmap Index Scan on createddate_idx (cost=0.00..2178.24 rows=195039 width=0)
> Index Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Now you can chat without downloading messenger. Click here to know how.
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Ashish Karalkar wrote:
> Thanks for your answer actually that was the part of full query here is the actuall plan
I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".
On this plan it looks like you need an index on otid. Or maybe not.
It's hard to tell with only an EXPLAIN.
Here is the OUPTPUT from EXPLAIN ANALYSE
HashAggregate (cost=5893012.31..5893014.28 rows=158 width=32) (actual time=829.511..829.511 rows=0 loops=1)
-> Hash Join (cost=215823.74..5892929.49 rows=5521 width=32) (actual time=829.502..829.502 rows=0 loops=1)
Hash Cond: ("outer".deliveryid = "inner".deliveryid)
-> Seq Scan on sms_new (cost=0.00..5036990.11 rows=128012086 width=8) (actual time=8.620..8.620 rows=1 loops=1)
Filter: ((otid)::text !~~ 'ERROR%'::text)
-> Hash (cost=215823.35..215823.35 rows=158 width=32) (actual time=820.865..820.865 rows=0 loops=1)
-> Bitmap Heap Scan on delivery (cost=2178.24..215823.35 rows=158 width=32) (actual time=820.857..820.857 rows=0 loops=1)
Recheck Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
Filter: ((taskid = 1024) AND (((remoteip)::text = '192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = '192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = '202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR ((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR ((remoteip)::text = '202.162.231.7'::text)))
-> Bitmap Index Scan on createddate_idx (cost=0.00..2178.24 rows=195039 width=0) (actual time=264.982..264.982 rows=208124 loops=1)
Index Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
Total runtime: 829.864 ms
(12 rows)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
5, 50, 500, 5000 - Store N number of mails in your inbox. Click here.
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
am Wed, dem 12.12.2007, um 11:44:58 +0000 mailte Ashish Karalkar folgendes:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
>
> Server is not using the index insted it chooses to take seq scan path. table is
> having @ 120 million rows
>
> here is the output from planner:
> Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> Filter: (mobile = 919820920858::bigint)
> -> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1
> width=8)
> Index Cond: ("outer".deliveryid = delivery.deliveryid)
> Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time
> zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone)
> AND ((keyword)::text = 'CRI'::text))
>
> column deliveryid has an index over it in sms_new table.
>
> can anybody please guide to force the usage of index
It's using an index on "deliveryid":
Index Scan using deliveryid_pkey
It is using index on deliveryid of table delivery but not of sms_new which is having 120 million rows.
deliveryid is also present in sms_new having index on it. instead of using index on sms_new its doing seq scan. I want to force index scan on sms_new
Do you have an index on "mobile"? Can you show us the table definition
and the output from EXPLAIN ANALYSE?
no index on mobile
output of EXPLAIn ANALYSE taking very long time.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Did you know? You can CHAT without downloading messenger. Click here
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
am Wed, dem 12.12.2007, um 12:25:20 +0000 mailte Ashish Karalkar folgendes:
> > here is the output from planner:
> > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> > Filter: (mobile = 919820920858::bigint)
>
> Do you have an index on "mobile"? Can you show us the table definition
> and the output from EXPLAIN ANALYSE?
>
> no index on mobile
Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
(cost=0.00..5027902.00
Can you see the problem?
So i will have to create index on mobile is taht so?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Unlimited freedom, unlimited storage. Get it now
On Dec 12, 2007 5:44 AM, Ashish Karalkar <ashish_postgre@yahoo.co.in> wrote:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
>
> Server is not using the index insted it chooses to take seq scan path. table
> is having @ 120 million rows
>
> here is the output from planner:
> Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> Filter: (mobile = 919820920858::bigint)
> -> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1
> width=8)
> Index Cond: ("outer".deliveryid = delivery.deliveryid)
> Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without
> time zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time
> zone) AND ((keyword)::text = 'CRI'::text))
>
> column deliveryid has an index over it in sms_new table.
>
> can anybody please guide to force the usage of index
An index on deliveryid IS being used. Says so right there in your
plan. Says it's expecting to get back exactly one row from it. Now,
sms_new is being seq scanned. While it might have 120M rows,
according to this plan, the query planner thinks it has 384 rows. So,
it's likely you aren't analyzing that table often enough. Just
guessing.
What's the query and table structure look like?