Hello, Thanks the nice people on irc my problem is fixed. I changed the following settings in the postgres.conf file: default_statistics_target = 5000 -> and I analyzed the tables after the change of course -> now I only got 2 plans anymore, in stead of 3 cpu_tuple_cost = 0.1 -> by setting this value the seq scans were stopped, and the better index_only scan / bitmap index scan were used for this query.
Thank you Robe and Mabe_ for helping me with this issue! wkr, Bert On Mon, Feb 18, 2013 at 2:42 PM, Bert <bier...@gmail.com> wrote: > Hello, > > yes, the tables are vacuumed every day with the following command: vacuum > analyze schema.table. > The last statistics were collected yesterday evening. I collected > statistics about the statistics, and I found the following: > table_name; starttime; runtime > "st_itemseat";"2013-02-17 23:48:42";"00:01:02" > "st_itemseat_45";"2013-02-17 23:35:15";"00:00:08" > "st_itemzone";"2013-02-17 23:35:33";"00:00:01" > > st_itemseat_45 is a child-partition of st_itemseat. > > They seem to be pretty much up to date I guess? > I also don't get any difference in the query plans when they are run in > the morning, or in the evening. > > I have also run the query with set seq_scan to off, and then I get the > following output: > Total query runtime: 12025 ms. > 20599 rows retrieved. > and the following plan: http://explain.depesz.com/s/yaJK > > These are 3 different plans. And the last one is blazingly fast. That's > the one I would always want to use :-) > > it's also weird that this is default plan for the biggest partition. But > the smaller the partition gets, the smaller the partition gets. > So I don't think it has anything to do with the memory settings. Since it > already chooses this plan for the bigger partitions... > > wkr, > Bert > > > On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz <fr...@frank.uvena.de>wrote: > >> Am 18.02.2013 10:43, schrieb Bert: >> > Does anyone has an idea what triggers this bad plan, and how I can fix >> it? >> >> Looks a bit like wrong statistics. Are the statistiks for your tables >> correct? >> >> Cheers, >> Frank >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > > > -- > Bert Desmet > 0477/305361 > -- Bert Desmet 0477/305361
