Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
On 4/12/2013 1:03 AM, Yang Zhang wrote: db= explain select * from lead where email = 'blah'; QUERY PLAN Seq Scan on lead (cost=0.00..319599.38 rows=1 width=5108) Filter: (email = 'blah'::text) try EXPLAIN ANALYZE .its more useful. my guess is, there's no statistics on this table, and doing an ANALYZE lead; would rectify this. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
Doesn't seem to be the case. This table has been around for a while and should have been auto-analyzed by now. But anyway: db= analyze lead; ANALYZE db= explain select * from lead where email = 'f...@blah.com'; QUERY PLAN --- Seq Scan on lead (cost=100.00..1319666.99 rows=1 width=5208) Filter: (email = 'f...@blah.com'::text) (2 rows) On Fri, Apr 12, 2013 at 1:13 AM, John R Pierce pie...@hogranch.com wrote: On 4/12/2013 1:03 AM, Yang Zhang wrote: db= explain select * from lead where email = 'blah'; QUERY PLAN Seq Scan on lead (cost=0.00..319599.38 rows=1 width=5108) Filter: (email = 'blah'::text) try EXPLAIN ANALYZE .its more useful. my guess is, there's no statistics on this table, and doing an ANALYZE lead; would rectify this. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
On 12 April 2013 10:45, Yang Zhang yanghates...@gmail.com wrote: explain select * from lead where email = 'f...@blah.com'; What about: explain analyze select * from lead where email = 'f...@blah.com'; -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
On 4/12/2013 1:45 AM, Yang Zhang wrote: db= explain select * from lead where email = 'f...@blah.com'; can you try explain analyze select * from lead where email = 'f...@blah.com'; ? -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
Yang Zhang yanghates...@gmail.com writes: db= explain select * from lead where email = 'blah'; QUERY PLAN Seq Scan on lead (cost=0.00..319599.38 rows=1 width=5108) Filter: (email = 'blah'::text) (2 rows) That's awfully odd. What data type is the email column? It seems possible also that the index on it is marked invalid. I'd have expected \d to tell you so, but maybe you're using a version of psql that doesn't know about that. It'd be interesting to look at select * from pg_index where indexrelid = 'index name here'::regclass; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
It's actually just `text`. I updated my SO question with some more info including explain analyze (no difference), \d, and your last incantation. Thanks! On Fri, Apr 12, 2013 at 7:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yang Zhang yanghates...@gmail.com writes: db= explain select * from lead where email = 'blah'; QUERY PLAN Seq Scan on lead (cost=0.00..319599.38 rows=1 width=5108) Filter: (email = 'blah'::text) (2 rows) That's awfully odd. What data type is the email column? It seems possible also that the index on it is marked invalid. I'd have expected \d to tell you so, but maybe you're using a version of psql that doesn't know about that. It'd be interesting to look at select * from pg_index where indexrelid = 'index name here'::regclass; regards, tom lane -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
Yang Zhang yanghates...@gmail.com writes: I updated my SO question with some more info including explain analyze (no difference), \d, and your last incantation. The question is being asked here, not in SO, and I find it rather impolite of you to expect me to go chasing off to some other forum to answer your question. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select
Apologies for that Tom. I will paste the information in line once I'm back at my computer. I do appreciate your help. On Fri, Apr 12, 2013 at 10:24 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yang Zhang yanghates...@gmail.com writes: I updated my SO question with some more info including explain analyze (no difference), \d, and your last incantation. The question is being asked here, not in SO, and I find it rather impolite of you to expect me to go chasing off to some other forum to answer your question. regards, tom lane -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
