Thanks Tom.
I mean if the instance is a test instance,
probably analysis_name_date_key can be dropped and the query can be
run again so as to check if it still returns the correct rows.
or create an index in parallel with the same col as
analysis_name_date_key and check if the optimizer choses the right
index.
and then come to conclusion of bad index.
Also is there an option where we can force a particular index to be used ?
i read somewhere the below query may help with detecting bad index, is
this correct?
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n,
pg_catalog.pg_index i
WHERE (i.indisvalid = false OR i.indisready = false) AND
i.indexrelid = c.oid AND c.relnamespace = n.oid AND
n.nspname != 'pg_catalog' AND
n.nspname != 'information_schema' AND
n.nspname != 'pg_toast'
Regards,
Vijay
On Tue, Mar 5, 2019 at 10:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Casey Deccio <casey@deccio.net> writes:
> >> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain <vjain@opentable.com> wrote:
> >> Can you run both the queries with
> >> “explain analyze select ....” and paste the output.
>
> > dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' order by date desc limit 20;
> > QUERY PLAN
>
> >
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > ----------------------------------
> > Limit (cost=0.57..81.74 rows=20 width=31) (actual time=0.540..0.541 rows=1 loops=1)
> > -> Index Scan Backward using analysis_name_date_key on analysis (cost=0.57..7760.25 rows=1912 width=31)
(actual
> > time=0.539..0.540 rows=1 loops=1)
> > Index Cond: ((name)::text = 'foo'::text)
> > Planning time: 6.728 ms
> > Execution time: 0.587 ms
> > (5 rows)
>
> Hm, so possibly corruption in that index? REINDEX might help.
>
> regards, tom lane
>