Search Postgresql Archives

Re: Non-unique index performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Sezai YILMAZ wrote:
Richard Huxton wrote:

OK - all very simple. And you've said there are about 580,000 rows.

test=> explain analyze select id,name from person where id in ('17201', '338191', '244319', '515209', '20415');

Why are you quoting integers?

I qouted them to use indexes. The other method is type casting the values to indexed column type. I prefer the quoting method.

Sorry - this is just plain wrong.

If you had an int8 column and a value such as 17, then PG looked at 17 and said Ha! an int4. Then it would not use your index. In such cases you could either cast the value to int8, or quote it (so that the planner decided its type later in the process).

This was never required if you had a value that was large enough to be int8 but not int4, nor when the column was int4. It is not an issue at all as of version 8.0.

So - if the column is a plain old int4 - just do things normally.

Hmm - for some reason it's expecting 14427 rows to be returned. If there were that many matches, then it might well be a better choice than going back and fore between the index and the table all the time.

So - we need to find out why it thinks there will be so many rows returned.

1. VACUUM FULL ANALYSE person;
2. re-run the explain


This solved the problem. Now it takes about 213 ms.

If you'd analysed frequently anyway, perhaps repeat the steps a few times and make sure the problem doesn't re-occur. If it does re-occur, you'll want to increase the column statistics like I'd described.

--
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux